{"id":8515,"date":"2019-09-07T03:10:03","date_gmt":"2019-09-07T10:10:03","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=8515"},"modified":"2023-12-30T03:46:05","modified_gmt":"2023-12-30T10:46:05","slug":"mysql-after-insert-trigger","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-triggers\/mysql-after-insert-trigger\/","title":{"rendered":"MySQL AFTER INSERT Trigger"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a MySQL AFTER INSERT<\/code> trigger to insert data into a table after inserting data into another table.<\/p>\n\n\n\n

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

MySQL AFTER INSERT<\/code> triggers are automatically invoked after an insert event occurs on the table.<\/p>\n\n\n\n

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

CREATE<\/span> TRIGGER<\/span> trigger_name\n    AFTER<\/span> INSERT<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\n        trigger_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<\/a><\/code> keywords.<\/p>\n\n\n\n
Second, use AFTER INSERT<\/code> clause to specify the time to invoke the trigger.<\/p>\n\n\n\n
Third, provide the name of the table on which you want to create the trigger after the ON<\/code> keyword.<\/p>\n\n\n\n
Finally, define the trigger body which consists of one or more statements that execute when the trigger is invoked.<\/p>\n\n\n\n
If the trigger body has multiple statements, you need 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    AFTER<\/span> INSERT<\/span>\n    ON<\/span> table_name FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    -- statements<\/span>\nEND<\/span>$$    \n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In an AFTER INSERT<\/code> trigger, you can access the NEW<\/code> values but you cannot change them. Also, you cannot access the OLD<\/code> values because there is no OLD<\/code> on INSERT<\/code> triggers.<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
MySQL AFTER INSERT trigger example<\/h2>\n\n\n\n
Consider the following AFTER INSERT<\/code> trigger example.<\/p>\n\n\n\n
Setting up a sample table<\/h3>\n\n\n\n
First, create a new table<\/a> called members<\/code>:<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> members;\n\nCREATE<\/span> TABLE<\/span> members (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    email VARCHAR<\/span>(255<\/span>),\n    birthDate DATE<\/span>,\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
Second, create another table called reminders<\/code> that stores reminder messages to members.<\/p>\n\n\n
DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> reminders;\n\nCREATE<\/span> TABLE<\/span> reminders (\n    id<\/span> INT<\/span> AUTO_INCREMENT,\n    memberId INT<\/span>,\n    message VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    PRIMARY KEY<\/span> (id<\/span>,memberId)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating AFTER INSERT trigger example<\/h3>\n\n\n\n
The following statement creates an AFTER INSERT<\/code> trigger that inserts a reminder into the reminders<\/code> table if the birth date of the member is NULL<\/code>.<\/p>\n\n\n
DELIMITER $$\n\nCREATE<\/span> TRIGGER<\/span> after_members_insert\nAFTER<\/span> INSERT<\/span>\nON<\/span> members FOR<\/span> EACH<\/span> ROW<\/span>\nBEGIN<\/span>\n    IF<\/span> NEW.birthDate IS<\/span> NULL<\/span> THEN<\/span>\n        INSERT<\/span> INTO<\/span> reminders(memberId, message)\n        VALUES<\/span>(new.id,CONCAT<\/span>('Hi '<\/span>, NEW.name, ', please update your date of birth.'<\/span>));\n    END<\/span> IF<\/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 this trigger:<\/p>\n\n\n\n
First, specify the name of the trigger after_members_insert<\/code> in the CREATE TRIGGER<\/code> clause:<\/p>\n\n\n
CREATE<\/span> TRIGGER<\/span> after_members_insert<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define the triggering event:<\/p>\n\n\n
AFTER INSERT<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, specify the table members<\/code> that the trigger is associated with:<\/p>\n\n\n
ON members 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, insert a new row into the reminders<\/code> table if the birth date of the member is NULL<\/code>.<\/p>\n\n\n\n
Testing the MySQL AFTER INSERT trigger<\/h3>\n\n\n\n
First, insert two rows<\/a> into the members<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> members(name<\/span>, email, birthDate)\nVALUES<\/span>\n    ('John Doe'<\/span>, 'john.doe@example.com'<\/span>, NULL<\/span>),\n    ('Jane Doe'<\/span>, 'jane.doe@example.com'<\/span>,'2000-01-01'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> * FROM<\/span> members;    <\/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, query data from reminders<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> reminders;    <\/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
We inserted two rows into the members<\/code> table. However, only the first row has a birth date value NULL<\/code>, therefore, the trigger inserted only one row into the reminders<\/code> table.<\/p>\n\n\n\n
In this tutorial, you have learned how to create a MySQL AFTER INSERT<\/code> trigger to insert data into a table after inserting data into 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 members<\/code> table:<\/p>\n\n\n