MySQL - After Insert Trigger

A Trigger is simply defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed whenever an event is performed. These events include SQL statements like INSERT, UPDATE and DELETE etc.

It is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired.

MySQL After Insert Trigger

The After Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Insert Trigger is executed right after a value is inserted into a database table.

A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically.

To elaborate, whenever an INSERT statement is executed in the database, the value is inserted into the table first followed by the trigger execution. Hence, one cannot update a newly inserted row using the AFTER INSERT trigger.

Syntax

Following is the syntax to create the AFTER INSERT trigger in MySQL −

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

Let us see an example demonstrating the AFTER INSERT trigger. In here, we are creating a new table named USERS, which contains the details of users of an application, using the following query −

CREATE TABLE USERS(
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(100) NOT NULL,
   AGE INT NOT NULL,
   BIRTH_DATE VARCHAR(100),
   PRIMARY KEY(ID)
);

Now, let us create another table 'PUSH_NOTIFICATIONS' that is used to store messages to send as push notifications to the users on their birthdays −

CREATE TABLE PUSH_NOTIFICATIONS(
   ID INT AUTO_INCREMENT,
   BIRTH_DATE VARCHAR(100),
   NOTIFICATIONS VARCHAR(255) NOT NULL,
   PRIMARY KEY(ID)
);

Using the following CREATE TRIGGER statement, create a new trigger after_trigger on the USERS table to insert values into the PUSH_NOTIFICATIONS table −

DELIMITER //
CREATE TRIGGER after_trigger AFTER INSERT ON USERS FOR EACH ROW
BEGIN
   IF NEW.BIRTH_DATE IS NOT NULL THEN
   INSERT INTO PUSH_NOTIFICATIONS VALUES
   (new.ID, new.BIRTH_DATE, CONCAT('Happy Birthday, ', NEW.NAME, '!'));
END IF;
END //
DELIMITER ;

Insert values into the USERS table using the regular INSERT statement as shown below −

INSERT INTO USERS (NAME, AGE, BIRTH_DATE) VALUES 
('Sasha', 23, '24/06/1999');
('Alex', 21, '12/01/2001');

Verification

To verify if the trigger has been executed, display the PUSH_NOTIFICATIONS table using the SELECT statement −