MySQL BEFORE UPDATE Trigger
Summary: in this tutorial, you will learn how to create a MySQL BEFORE UPDATE trigger to validate data before it is updated to a table.
Introduction to MySQL BEFORE UPDATE triggers
MySQL BEFORE UPDATE triggers are invoked automatically before an update event occurs on the table associated with the triggers.
Here is the syntax of creating a MySQL BEFORE UPDATE trigger:
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body
Code language: SQL (Structured Query Language) (sql)
In this syntax:
First, specify the name of the trigger that you want to create after the CREATE TRIGGER keywords.
Second, use BEFORE UPDATE clause to specify the time to invoke the trigger.
Third, specify the name of the table to which the trigger belongs after the ON keyword.
Finally, specify the trigger body which contains one or more statements.
If you have more than one statement in the trigger_body, you need to use the BEGIN END block. In addition, you need to change the default delimiter as follows:
DELIMITER $$BEFORE UPDATE
ON table_name FOR EACH ROW
BEGIN
— statements
END$$
Code language: SQL (Structured Query Language) (sql)
In a BEFORE UPDATE trigger, you can update the NEW values but cannot update the OLD values.
MySQL BEFORE UPDATE trigger example
Let’s look at an example of using a BEFORE UPDATE trigger.
Setting up a sample table
First, create a new table called sales to store sales volumes:
DROP TABLE IF EXISTS sales;id INT AUTO_INCREMENT,
product VARCHAR(100) NOT NULL,
quantity INT NOT NULL DEFAULT 0,
fiscalYear SMALLINT NOT NULL,
fiscalMonth TINYINT NOT NULL,
CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
CHECK(fiscalYear BETWEEN 2000 and 2050),
CHECK (quantity >=0),
UNIQUE(product, fiscalYear, fiscalMonth),
PRIMARY KEY(id)
);
Code language: SQL (Structured Query Language) (sql)
Second, insert some rows into the sales table:
INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth)
VALUES
('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1),
('1969 Corvair Monza', 150,2020,1),
('1970 Plymouth Hemi Cuda', 200,2020,1);
Code language: SQL (Structured Query Language) (sql)
Third, query data from the sales table to verify the insert:
SELECT * FROM sales;
Code language: SQL (Structured Query Language) (sql)
Creating BEFORE UPDATE trigger example
The following statement creates a BEFORE UPDATE trigger on the sales table.
DELIMITER $$BEFORE UPDATE
ON sales FOR EACH ROW
BEGIN
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT(‘The new quantity ‘,
NEW.quantity,
‘ cannot be 3 times greater than the current quantity ‘,
OLD.quantity);
SIGNAL SQLSTATE ‘45000’
SET MESSAGE_TEXT = errorMessage;
END IF;
END $$
Code language: SQL (Structured Query Language) (sql)
The trigger is automatically fired before an update event occurs for each row in the sales table.
If you update the value in the quantity column to a new value that is 3 times greater than the current value, the trigger raises an error and stops the update.
Let’s examine the trigger in details:
First, the name of the trigger is before_sales_update specified in the CREATE TRIGGER clause:
CREATE TRIGGER before_sales_update
Code language: SQL (Structured Query Language) (sql)
Second, the triggering event is:
BEFORE UPDATE
Code language: SQL (Structured Query Language) (sql)
Third, the table that the trigger associated with is sales:
ON sales FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
Fourth, declare a variable and set its value to an error message. Note that, in the BEFORE TRIGGER, you can access both old and new values of the columns via OLD and NEW modifiers.
DECLARE errorMessage VARCHAR(255);
SET errorMessage = CONCAT('The new quantity ',
NEW.quantity,
' cannot be 3 times greater than the current quantity ',
OLD.quantity);
Code language: SQL (Structured Query Language) (sql)
Note that we use the CONCAT() function to form the error message.
Finally, use the IF-THEN statement to check if the new value is 3 times greater than old value, then raise an error by using the SIGNAL statement:
IF new.quantity > old.quantity * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
Code language: SQL (Structured Query Language) (sql)
Testing the MySQL BEFORE UPDATE trigger
First, update the quantity of the row with id 1 to 150:
UPDATE sales
SET quantity = 150
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)
It worked because the new quantity does not violate the rule.
Second, query data from the sales table to verify the update:
SELECT * FROM sales;
Code language: SQL (Structured Query Language) (sql)
Third, update the quantity of the row with id 1 to 500:
UPDATE sales
SET quantity = 500
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)
MySQL issued this error:
Error Code: 1644. The new quantity 500 cannot be 3 times greater than the current quantity 150
Code language: SQL (Structured Query Language) (sql)
In this case, the trigger found that the new quantity caused a violation and raised an error.
Finally, use the SHOW ERRORS to display the error:
SHOW ERRORS;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to create a MySQL BEFORE UPDATE trigger to validate data before it is updated to a table.