{"id":8509,"date":"2019-09-07T02:03:59","date_gmt":"2019-09-07T09:03:59","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8509"},"modified":"2023-10-06T18:33:07","modified_gmt":"2023-10-07T01:33:07","slug":"mysql-before-insert-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-before-insert-trigger\/","title":{"rendered":"MySQL BEFORE INSERT Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL BEFORE INSERT<\/code> trigger to maintain a summary table of another table.<\/p>\n\n\n\n

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

MySQL BEFORE INSERT<\/code> triggers<\/a> are automatically fired before an insert<\/a> event occurs on the table.<\/p>\n\n\n\n

The following illustrates the basic syntax of creating a MySQL BEFORE INSERT<\/code> trigger:<\/p>\n\n\n

CREATE<\/span> TRIGGER<\/span> trigger_name\n    BEFORE<\/span> INSERT<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\ntrigger_body;\n<\/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
Second, use BEFORE INSERT<\/code> clause to specify the time to invoke the trigger.<\/p>\n\n\n\n
Third, specify the name of the table that the trigger is associated with after the ON<\/code> keyword.<\/p>\n\n\n\n
Finally, specify the trigger body which contains one or more SQL statements that execute when the trigger is invoked.<\/p>\n\n\n\n
If you have multiple statements in the trigger_body<\/code>, you have to use the BEGIN END<\/code> block and change the default delimiter<\/a>:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> trigger_name\n    BEFORE<\/span> INSERT<\/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
Note that in a BEFORE INSERT<\/code> trigger, you can access and change the NEW<\/code> values. However, you cannot access the OLD<\/code> values because OLD<\/code> values obviously do not exist.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL BEFORE INSERT trigger example<\/h2>\n\n\n\n
We will create a BEFORE INSERT<\/code> trigger to maintain a summary table from another table.<\/p>\n\n\n\n
Setting up a sample table<\/h3>\n\n\n\n
First, create a new table<\/a> called WorkCenters<\/code>:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> WorkCenters;\n\nCREATE<\/span> TABLE<\/span> WorkCenters (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    capacity<\/span> INT<\/span> NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create another table called WorkCenterStats<\/code> that stores the summary of the capacity of the work centers:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> WorkCenterStats;\n\nCREATE<\/span> TABLE<\/span> WorkCenterStats(\n    totalCapacity INT<\/span> NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating BEFORE INSERT trigger example<\/h3>\n\n\n\n
The following trigger updates the total capacity of the WorkCenterStats<\/code> table before a new work center is inserted into the WorkCenter<\/code> table:<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> before_workcenters_insert\nBEFORE<\/span> INSERT<\/span>\nON<\/span> WorkCenters FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    DECLARE<\/span> rowcount INT<\/span>;\n    \n    SELECT<\/span> COUNT<\/span>(*) \n    INTO<\/span> rowcount\n    FROM<\/span> WorkCenterStats;\n    \n    IF rowcount > 0 THEN\n        UPDATE<\/span> WorkCenterStats\n        SET<\/span> totalCapacity = totalCapacity + new.capacity;\n    ELSE\n        INSERT<\/span> INTO<\/span> WorkCenterStats(totalCapacity)\n        VALUES<\/span>(new.capacity);\n    END<\/span> IF<\/span>; \n\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 this trigger:<\/p>\n\n\n\n
First, the name of the trigger is before_workcenters_insert<\/code> specified in the CREATE TRIGGER<\/code> clause:<\/p>\n\n\n
CREATE<\/span> TRIGGER<\/span> before_workcenters_insert\n<\/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 INSERT<\/span>\n<\/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 WorkCenters<\/code> table:<\/p>\n\n\n
ON WorkCenters FOR EACH ROW<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, inside the trigger body, we check for any row in the WorkCenterStats<\/code> table.<\/p>\n\n\n\n
If the table WorkCenterStats<\/code> has a row, the trigger adds the capacity to the totalCapacity<\/code> column. Otherwise, it inserts a new row into the WorkCenterStats<\/code> table.<\/p>\n\n\n\n
Testing the MySQL BEFORE INSERT trigger<\/h3>\n\n\n\n
First, insert a new row<\/a> into the WorkCenter<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> WorkCenters(name<\/span>, capacity<\/span>)\nVALUES<\/span>('Mold Machine'<\/span>,100<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> WorkCenterStats;    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
The trigger has been invoked and inserted a new row into the WorkCenterStats<\/code> table.<\/p>\n\n\n\n
Third, insert a new work center:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> WorkCenters(name<\/span>, capacity<\/span>)\nVALUES<\/span>('Packing'<\/span>,200<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, query data from the WorkCenterStats<\/code>:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> WorkCenterStats;\n<\/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
The trigger has updated the total capacity from 100 to 200 as expected.<\/p>\n\n\n\n
Note that to properly maintain the summary table WorkCenterStats<\/code>, you should also create triggers to handle update and delete events on the WorkCenters<\/code> table.<\/p>\n\n\n\n
In this tutorial, you have learned how to create a MySQL BEFORE INSERT<\/code> trigger to maintain a summary table of another 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
Second, query data<\/a> from the WorkCenterStats<\/code> table:<\/p>\n\n\n
First, specify the name of the trigger that you want to create in the CREATE TRIGGER<\/a><\/code> clause.<\/p>\n\n\n\n