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 for 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 $$
CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
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;
CREATE TABLE 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 $$
CREATE TRIGGER before_sales_update
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);
IF new.quantity > old.quantity * 3 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = errorMessage;
END IF;
END $$
DELIMITER ;
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 detail:
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 is 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 the 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 did 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.