{"id":8479,"date":"2019-09-06T21:19:00","date_gmt":"2019-09-07T04:19:00","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8479"},"modified":"2023-12-29T21:26:34","modified_gmt":"2023-12-30T04:26:34","slug":"mysql-before-update-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-before-update-trigger\/","title":{"rendered":"MySQL BEFORE UPDATE Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL BEFORE UPDATE<\/code> trigger to validate data before it is updated to a table.<\/p>\n\n\n\n

Introduction to MySQL BEFORE UPDATE triggers<\/h2>\n\n\n\n

MySQL BEFORE UPDATE<\/code> triggers<\/a> are invoked automatically before an update<\/a> event occurs on the table associated with the triggers.<\/p>\n\n\n\n

Here is the syntax for creating a MySQL BEFORE UPDATE<\/code> trigger:<\/p>\n\n\n

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