MySQL AFTER INSERT Trigger
Summary: in this tutorial, you will learn how to create a MySQL AFTER INSERT trigger to insert data into a table after inserting data into another table.
Introduction to MySQL AFTER INSERT triggers
MySQL AFTER INSERT triggers are automatically invoked after an insert event occurs on the table.
The following shows the basic syntax of creating a MySQL AFTER INSERT trigger:
CREATE TRIGGER trigger_name
AFTER INSERT
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 AFTER INSERT clause to specify the time to invoke the trigger.
Third, specify the name of the table on which you want to create the trigger after the ON keyword.
Finally, specify the trigger body which consists of one or more statements that execute when the trigger is invoked.
In case the trigger body has multiple statements, you need to use the BEGIN END block and change the default delimiter:
DELIMITER $$AFTER INSERT
ON table_name FOR EACH ROW
BEGIN
— statements
END$$
Code language: SQL (Structured Query Language) (sql)
In an AFTER INSERT trigger, you can access the NEW values but you cannot change them. Also, you cannot access the OLD values because there is no OLD on INSERT triggers.
MySQL AFTER INSERT trigger example
Consider the following AFTER INSERT trigger example.
Setting up a sample table
First, create a new table called members:
DROP TABLE IF EXISTS members;id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(255),
birthDate DATE,
PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)
Second, create another table called reminders that stores reminder messages to members.
DROP TABLE IF EXISTS reminders;id INT AUTO_INCREMENT,
memberId INT,
message VARCHAR(255) NOT NULL,
PRIMARY KEY (id , memberId)
);
Code language: SQL (Structured Query Language) (sql)
Creating AFTER INSERT trigger example
The following statement creates an AFTER INSERT trigger that inserts a reminder into the reminders table if the birth date of the member is NULL.
DELIMITER $$AFTER INSERT
ON members FOR EACH ROW
BEGIN
IF NEW.birthDate IS NULL THEN
INSERT INTO reminders(memberId, message)
VALUES(new.id,CONCAT(‘Hi ‘, NEW.name, ‘, please update your date of birth.’));
END IF;
END$$
Code language: SQL (Structured Query Language) (sql)
In this trigger:
First, the name of the trigger is after_members_insert specified in the CREATE TRIGGER clause:
CREATE TRIGGER after_members_insert
Code language: SQL (Structured Query Language) (sql)
Second, the triggering event is:
AFTER INSERT
Code language: SQL (Structured Query Language) (sql)
Third, the table that the trigger associated with is members table:
ON members FOR EACH ROW
Code language: SQL (Structured Query Language) (sql)
Finally, inside the trigger body, insert a new row into the reminder table if the birth date of the member is NULL.
Testing the MySQL AFTER INSERT trigger
First, insert two rows into the members table:
INSERT INTO members(name, email, birthDate)
VALUES
('John Doe', 'john.doe@example.com', NULL),
('Jane Doe', 'jane.doe@example.com','2000-01-01');
Code language: SQL (Structured Query Language) (sql)
Second, query data from the members table:
SELECT * FROM members;
Code language: SQL (Structured Query Language) (sql)
Third, query data from reminders table:
SELECT * FROM reminders;
Code language: SQL (Structured Query Language) (sql)
We inserted two rows into the members table. However, only the first row that has a birth date value NULL, therefore, the trigger inserted only one row into the reminders table.
In this tutorial, you have learned how to create a MySQL AFTER INSERT trigger to insert data into a table after inserting data into another table.