MySQL AFTER INSERT Trigger By Practical Examples

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_bodyCode 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, provide the name of the table on which you want to create the trigger after the ON keyword.

Finally, define the trigger body which consists of one or more statements that execute when the trigger is invoked.

If the trigger body has multiple statements, you need to use the BEGIN END block and change the default delimiter:

DELIMITER $$

CREATE TRIGGER trigger_name
    AFTER INSERT
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END$$    

DELIMITER ;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;

CREATE TABLE 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;

CREATE TABLE 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 $$

CREATE TRIGGER after_members_insert
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$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

In this trigger:

First, specify the name of the trigger after_members_insert in the CREATE TRIGGER clause:

CREATE TRIGGER after_members_insertCode language: SQL (Structured Query Language) (sql)

Second, define the triggering event:

AFTER INSERTCode language: SQL (Structured Query Language) (sql)

Third, specify the table members that the trigger is associated with:

ON members FOR EACH ROWCode language: SQL (Structured Query Language) (sql)

Finally, inside the trigger body, insert a new row into the reminders 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', '[email protected]', NULL),
    ('Jane Doe', '[email protected]','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 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.

Was this tutorial helpful?