How to use MySQL After Trigger

In MySQL, an AFTER trigger is a kind of trigger that is executed after a specified event, specially after an INSERT, UPDATE, or DELETE operation on a table. It permits you to define actions or processes that must be performed automatically after the event occurs. In this article, we will learn how to use MySQL AFTER trigger.

Syntax and structure of MySQL AFTER trigger:

CREATE TRIGGER triggerName
AFTER triggerEvent
ON tableName
FOR EACH ROW
BEGIN
    -- SQL statements
END;

triggerName: It is the name you give to the trigger.

triggerTime: It indicates that the trigger should be executed after the specified event.

triggerEvent: It specifies the trigger event that will activate the trigger.

tableName: It is the name of the table on which the trigger is being created.

FOR EACH ROW: It signifies that the trigger will be executed for each row affected by the triggering operation.

BEGIN and END: The trigger logic and actions should be written between the BEGIN and END statements.

Few points to remember about MySQL AFTER triggers:

An AFTER trigger is associated with a specific event, such as an INSERT, UPDATE, or DELETE, and is executed after the event has completed; that means if you create an AFTER INSERT trigger, it will be triggered and executed after a new row is added into the table.
When you create an AFTER trigger, you specify the table name, the event (INSERT, UPDATE, DELETE), and the trigger time (AFTER). You also define the actions or statements that should be executed within the body of the trigger.
AFTER triggers can be used for many purposes, such as enforcing data integrity constraints, logging changes, updating related tables, or performing additional calculations or validations.
MySQL allows multiple AFTER triggers to be defined for a single event on a table. The order of execution is determined by the order in which triggers were created. You can use the 'SHOW TRIGGERS' command to view the existing triggers and their execution order.
If an error occurs within an AFTER trigger, then in that case, execution of the trigger will be stopped, and any changes made within the trigger will be rolled back, which ensures that the database remains in a consistent state and prevents incomplete data modifications.

Types of MySQL AFTER trigger:

There are three kinds of MySQL AFTER trigger: AFTER INSERT trigger, AFTER UPDATE trigger, and AFTER DELETE trigger.

1. AFTER INSERT trigger:

In MySQL, an AFTER INSERT trigger is a database object that permits you to execute actions automatically after a new record is added to the table.

To create an AFTER INSERT trigger in MySQL, you have to use the CREATE TRIGGER statement, specifying the table name, table event (INSERT), and trigger timing (AFTER). The trigger body can contain one or more SQL statements enclosed within the BEGIN and END keywords.

Syntax:

CREATE TRIGGER afterInsertTrigger
AFTER INSERT ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

In the above example, "afterInsertTrigger" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is created, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example 1:

Let us create two tables: 'school_student', which has fields such as Id, Name, Class, Age & Address, and 'school_audit', which has fields such as Id and audit_description.

Use the given command to create the 'school_student' table:

CREATE TABLE school_student (
          Id INT PRIMARY KEY,
          Name VARCHAR(45),
          Class INT,
          Age INT,
          Address VARCHAR(45)
        );

The table is created, and it will look like it is shown below:

Id	Name	Class	Age	Address

Now, we will insert values in the 'school_student' table,

INSERT INTO school_student
VALUES(301, "Vaishnav", 8, 12, 'Mathura'),
(302, "Amisha", 9, 13, "Noida"),
(303, "Disha", 8, 12, "Lucknow"),
(304, "Kuna", 10, 14, "Lucknow"),
(305, "Kairav", 10, 15, "Lucknow");

After inserting the values in the 'school_student' table, it will look like it is shown below:

Id	Name	Class	Age	Address
301	Vaishnav	8	12	Mathura
302	Amisha	9	13	Noida
303	Disha	8	12	Lucknow
304	Kunal	10	14	Lucknow
305	Kairav	10	15	Lucknow

Now, we will create the 'school_audit' table using the following command:

CREATE TABLE school_audit (
          Id INT PRIMARY KEY,
          audit_description VARCHAR(120)
        );

The table is created, and it will look like it is shown below:

Id	audit_description

Now we will create a trigger called 'after_insert_school', which will be set to insert the audit details into the 'school_audit' table when a new row is added to the 'school_student' table. Use the following statements to create a trigger:

CREATE TRIGGER after_insert_school
AFTER INSERT ON school_student
FOR EACH ROW
    INSERT INTO school_audit
    VALUES(1, concat("A new student detail has been inserted", date_format(now(), '%d, %m, %y %h:%i:%s %p')));

After creating a trigger, execute it.

Now, we will insert the values in the 'school_student' table,

INSERT INTO school_student
VALUES(306, 'Arhan', 10, 15, 'Noida');

After inserting the values in the 'school_student' table, we will use the given statement to see the table:

The 'school_student' table will appear as shown below;

As we have applied the trigger on the 'school_student' table, with the insertion of each new row in the 'school_student' table, the date and time are inserted into the 'school_audit' table.

We will use the following statement to view the 'school_audit' table:

As you can see below, the date and time are automatically inserted in the 'school_audit' table.

Example 2:

Let us create two tables: 'factory_worker', which has fields such as Id, Name, Age, Address & Wage and 'factory_audit', which has fields such as Id and audit_description.

Use the given command to create the 'factory_worker' table:

CREATE TABLE factory_worker (
          Id INT PRIMARY KEY,
          Name VARCHAR(45),
          Age INT,
          Address VARCHAR(45),
          Wage INT
        );

The table is created, and it will look like it is shown below:

Id	Name	Age	Address	Wage

Now, we will insert values in the 'factory_worker' table,

INSERT INTO factory_worker 
VALUES(401, "Nakul", 25, "Roorkee", 15000),
(402, "Aisha", 26, "Lucknow", 17000),
(403, "Dipika", 30, "Meerut", 16000),
(404, "Karan", 26, "Roorkee", 18000),
(405, "Avinash", 32, "Noida", 19000);

After inserting the values in the 'factory_worker' table, it will look like it is shown below:

Id	Name	Age	Address	Wage
401	Nakul	25	Roorkee	15000
402	Aisha	26	Lucknow	17000
403	Dipika	30	Meerut	16000
404	Karan	26	Roorkee	18000
405	Avinash	32	Noida	19000

Now, we will create the 'factory_audit' table using the following command:

CREATE TABLE factory_audit (
          Id INT PRIMARY KEY,
          audit_description VARCHAR(150)
        );

The table is created, and it will look like it is shown below:

Id	audit_description

Now we will create a trigger called 'after_insert_factory', which will be set to insert the audit details into the 'factory_audit' table when a new row is added to the 'factory_worker' table. Use the following statements to create a trigger:

CREATE TRIGGER after_insert_factory
AFTER INSERT ON factory_worker
FOR EACH ROW
    INSERT INTO factory_audit VALUES(1, concat("A new worker detail has been inserted", date_format(now(), '%d, %m, %y %h:%i:%s %p')));

After creating a trigger, execute it.

Now, we will insert the values in the 'school_student' table,

INSERT INTO factory_worker
VALUES(406, 'Kritika', 35, 'Meerut', 18000);

After inserting the values in the 'factory_worker' table, use the given command to view the table:

The 'factory_worker' table will appear as shown below.

As we have applied the trigger on the 'factory_worker' table, with the insertion of each new row in the 'factory_worker' table, the date and time are inserted into the 'factory_audit' table.

We will use the following statement to view the 'factory_audit' table:

As you can see below, the date and time are automatically inserted in the 'factory_audit' table.

2. AFTER UPDATE trigger:

In MySQL, an AFTER UPDATE trigger is a database object that permits you to execute actions automatically after a new record is added to the table. To create an AFTER UPDATE trigger in MySQL, you must use the CREATE TRIGGER statement, specifying the table name, trigger timing (AFTER), trigger event (UPDATE), and the trigger body. The trigger body consists of one or more SQL statements enclosed within the BEGIN and END keywords.

Syntax:

CREATE TRIGGER afterUpdateTrigger
AFTER UPDATE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

In the above example, "afterUpdateTrigger" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is created, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example1:

Let us create the first table called 'snacks_details', which has fields such as Id, Name, snacks_type, Quantity, Price, and Calories.

Use the following command to create the 'snacks_details' table:

CREATE TABLE snacks_details(
Id INT PRIMARY KEY,
Name VARCHAR(50),
snacks_type VARCHAR(40),
Quantity INT,
Price INT,
Calories FLOAT
);

The table is created, and it will look like it is shown below:

Id	Name	snacks_type	Quantity	Price	Calories

Now, we will insert the values in the 'snacks_details' table using the given command:

INSERT INTO snacks_details
VALUES(1, "Samosa", "Vegan", 50, 2, 20.5),
(2, "Kachori", "Vegan", 100, 4, 100.2),
(3, "Cookies", "Non-Vegetarian", 200, 10, 50.4),
(4, "Dahi Vada", "Vegetarian", 250, 2, 24.5),
(5, "Panipuri", "Vegan", 120, 12, 50.2);

After inserting the values in the 'snacks_details' table, it will appear as shown below:

Id	Name	snacks_type	Quantity	Price	Calories
1	Samosa	Vegan	2	50	20.5
2	Kachori	Vegan	4	100	100.2
3	Cookies	Non- Vegetarian	10	200	50.4
4	Dahi Vada	Vegetarian	2	250	24.5
5	Panipuri	Vegan	12	120	50.2

Now, we will create the second table called 'price_details', which has fields like Id, snacks_id, old_price, new_price, and updated_date_time.

CREATE TABLE price_details(
Id INT PRIMARY KEY AUTO_INCREMENT,
snacks_id INT,
old_price INT,
new_price INT,
updated_date_time DATETIME
);

The table is created, and it will look like it is shown below:

Id	snacks_id	old_price	new_price	updated_date_time

Now, we will create a trigger called 'after_update_price', which will be used to add a new row in the 'price_details' table whenever the quantity is updated in the 'snacks_details' table. We are going to use the following statements to create the trigger:

DELIMITER //
CREATE TRIGGER after_update_price
AFTER UPDATE ON snacks_details
FOR EACH ROW
BEGIN
IF OLD.price!=NEW.price THEN
INSERT INTO price_details(snacks_id, old_price, new_price, updated_date_time)
VALUES(OLD.id, OLD.price, NEW.price, NOW());
END IF;
END//

After creating a trigger, execute it.

Now, we will update the price of snacks of 'Id=1' in the 'snacks_details' table using the following statements:

UPDATE snacks_details
SET Price = 60
WHERE Id = 1;

After executing the above statement, let us check the 'snacks_details' table using the following statement:

As you can see below, the price of 'Id=1' has been changed.

Now, let us check the 'price_details' table using the following statement:

As you can see below, a row has been inserted in the table with old and new price details.

Let us again update the price of snacks on the 'snacks_details' table. This time we will update the price of snacks of 'Id=4' using the following statements:

UPDATE snacks_details
SET Price = 350
WHERE Id = 4;

After executing the above statement, let us check the 'snacks_details' table using the following statement:

As you can see below, the price of 'Id=4' has been changed.

Now, let us check the 'price_details' table using the following statement:

As you can see below, a new row has been inserted in the table with old and new price details.

Example 2:

Let us create the first table called 'electrical_appliances', which has fields such as Id, Name, Quantity, manufacturing_year, and Price.

Use the following command to create the 'electrical_appliances' table:

CREATE TABLE electrical_appliances(
Id INT PRIMARY KEY,
Name VARCHAR(60),
Quantity INT,
manufacturing_year YEAR,
Price INT
);

The table is created, and it will look like it is shown below:

Id	Name	Quantity	manufacturing_year	Price

Now, we will insert the values in the 'electrical_appliances' table using the given command:

INSERT INTO electrical_appliances
VALUES(1, "Television", 2, 2023, 45000),
(2, "Refrigerator", 4, 2022, 60000),
(3, "Oven", 5, 2022, 20000),
(4, "Coffee Maker", 6, 2023, 35000),
(5, "Blender", 4, 2023, 12000);

After inserting the values in the 'electrical_appliances' table, it will appear as shown below:

Id	Name	Quantity	manufacturing_year	Price
1	Television	2	2023	45000
2	Refrigerator	4	2022	60000
3	Oven	5	2022	20000
4	Coffee Maker	6	2023	35000
5	Blender	4	2023	12000

Now, we will create the second table called 'quantity_details', which has fields like Id, appliances_id, old_quantity, new_quantity, and updated_date_time.

CREATE TABLE quantity_details(
Id INT PRIMARY KEY AUTO_INCREMENT,
appliances_id INT,
old_quantity INT,
new_quantity INT,
updated_date_time DATETIME
);

The table is created, and it will look like it is shown below:

Id	appliances_id	old_quantity	new_quantity	updated_date_time

Now, we will create a trigger called 'after_update_quantity', which will be used to add a new row in the 'quantity_details' table whenever the quantity is updated in the 'electrical_appliances' table. We are going to use the following statements to create the trigger:

DELIMITER //
CREATE TRIGGER after_update_quantity
AFTER UPDATE ON electrical_appliances
FOR EACH ROW
BEGIN
IF OLD.quantity!=NEW.quantity THEN
INSERT INTO quantity_details(appliances_id, old_quantity, new_quantity, updated_date_time)
VALUES(OLD.id, OLD.quantity, NEW.quantity, NOW());
END IF;
END//

After creating a trigger, execute it.

Now, we will update the quantity of 'Id=2' in the 'electrical_appliances' table using the following statements:

UPDATE electrical_appliances
SET Quantity = 6
WHERE Id = 2;

After executing the above statement, let us check the 'electrical_appliances' table using the following statement:

As you can see below, the quantity of 'Id=2' has been changed.

Now, let us check the 'quantity_details' table using the following statement:

As you can see below, a row has been inserted in the table with old and new price details.

Let us again update the quantity of the 'electrical_appliances' table. This time we will update the quantity of 'Id=5' using the following statements:

UPDATE electrical_appliances
SET Quantity = 5
WHERE Id = 5;

After executing the above statement, let us check the 'electrical_appliances' table using the following statement:

As you can see below, the quantity of 'Id=5' has been changed.

Now, let us check the 'quantity_details' table using the following statement:

As you can see below, a new row has been inserted in the table with old and new quantity details.

3. AFTER DELETE trigger:

In MySQL, an AFTER DELETE trigger is a database object that permits you to execute actions automatically after a new record is added to the table.

To create an AFTER DELETE trigger in MySQL, you have to use the CREATE TRIGGER statement, specifying the table name, table event (DELETE), and trigger timing (AFTER). The trigger body can contain one or more SQL statements enclosed within the BEGIN and END keywords.

Syntax:

CREATE TRIGGER afterInsertTrigger
AFTER DELETE ON tableName
FOR EACH ROW
BEGIN
    -- Trigger statements here
END;

In the above example, "afterDeleteTrigger" is the name you assign to the trigger, "tableName" is the name of the table on which the trigger is created, and "columnName" is the name of a particular column within the table. You can define your custom logic and functions inside BEGIN and END blocks.

Example 1:

Let us create a table called 'customers', which has fields such as Id, Name, Age, and Address.

Use the given command to construct the 'customers' table:

CREATE TABLE customers(
Id INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Address VARCHAR(50)
);

The table is created, and it will look like you can see below:

Id	Name	Age	Address

Now, we will insert the values in the 'customers' table using the given command:

INSERT INTO customers
VALUES(1, "Rahul", 21, "Kanpur"),
(2, "Ashima", 24, "Kanpur"),
(3, "Sahil", 20, "Meerut"),
(4, "Nisha", 31, "Mumbai"),
(5, "Mihir", 29, "Lucknow");

After inserting the values in the 'customers' table, it will look like you can see below:

Id	Name	Age	Address
1	Rahul	21	Kanpur
2	Ashima	24	Kanpur
3	Sahil	20	Meerut
4	Nisha	31	Mumbai
5	Mihir	29	Lucknow

Now, we will create another table named 'backup_data', which will store the deleted data from the 'customers' table.

The 'backup_data' table consists of fields like user_name and comments.

CREATE TABLE backup_data(
user_name VARCHAR(60),
comments VARCHAR(260)
);

The table is created; it will look like you can see below:

user_name	comments

We will create a trigger called 'data_delete' using the given statements:

DELIMITER //
CREATE TRIGGER data_delete
AFTER DELETE
ON customers
FOR EACH ROW
BEGIN
INSERT INTO backup_data
VALUES (current_user(), CONCAT('Deleted data-', old.id, ' ', old.name, ' ', old.age, ' ', old.address, ' ', 'Deleted on ', NOW()));
END//

After creating a trigger, execute it.

Now, we will use the given command to delete data from the 'customers' table:

DELETE FROM customers
WHERE Id=2;

Now, we will look at the 'customers' table using the given statement:

As you can see below, the data of 'Id=2' is deleted.

Now we will check the 'backup_data' table to see whether the deleted data has been stored in the table or not.

We will use the given statement to see the 'backup_data' table:

As you can see below, the deleted data has been stored in the table.

Example 2:

Let us create a table called 'staff', which has fields such as Id, Name, Age, Address, and Salary.

Use the given command to construct the 'staff' table:

CREATE TABLE staff(
Id INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Address VARCHAR(50),
Salary INT
);

The table is created, and it will look like you can see below:

Id	Name	Age	Address	Salary

Now, we will insert the values in the 'staff' table using the given command:

INSERT INTO staff
VALUES(1, "Aryan", 25, "Meerut", 25000),
(2, "Karishma", 26, "Meerut", 26000),
(3, "Muskan", 27, "Kanpur", 26000),
(4, "Sameer", 28, "Lucknow", 28000),
(5, "Ansh", 28, "Mumbai", 28000);

After inserting the values in the 'staff' table, it will look like you can see below:

Id	Name	Age	Address	Salary
1	Aryan	25	Meerut	25000
2	Karishma	26	Meerut	26000
3	Muskan	27	Kanpur	26000
4	Sameer	28	Lucknow	28000
5	Ansh	28	Mumbai	28000

Now, we will create another table named 'backup', which will store the deleted data from the 'staff' table.

The 'backup' table consists of fields like User and Comments.

CREATE TABLE backup(
User VARCHAR(65),
Comments VARCHAR(275)
);

The table is created, and it will look like you can see below:

User	Comments

We will create a trigger called 'after_delete' using the given statements:

DELIMITER //
CREATE TRIGGER after_delete
AFTER DELETE
ON staff
FOR EACH ROW
BEGIN
INSERT INTO backup
VALUES (current_user(), CONCAT('Deleted data-', old.id, ' ', old.name, ' ', old.age, ' ', old.address, ' ', old.salary, ' ', 'Deleted on ', NOW()));
END//

After creating a trigger, execute it.

Now, we will use the given command to delete data from the 'staff' table:

DELETE FROM staff
WHERE Id=1;

Now, we will look at the 'staff' table using the given statement:

As you can see below, the data of 'Id=1' is deleted.

Now we will check the 'backup' table to see whether the deleted data has been stored in the table or not. We will use the given statement to see the 'backup' table:

As you can see below, the deleted data has been stored in the table.

Conclusion:

In this article, we have learned how to use MySQL AFTER trigger. We have learned that there are three kinds of AFTER trigger: AFTER UPDATE trigger, AFTER INSERT trigger, and AFTER DELETE trigger. We have understood each type of AFTER trigger properly with the help of examples.

Next TopicAzure MySQL

← prev next →