AFTER DELETE Trigger - MySQL Triggers

In MySQL, a trigger is a set of SQL statements that are automatically executed in response to a specific event, such as an INSERT, UPDATE, or DELETE operation on a table. In this tutorial, we'll specifically discuss the AFTER DELETE trigger in MySQL.

Syntax

The syntax for creating an AFTER DELETE trigger in MySQL is as follows:

CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name
FOR EACH ROW
BEGIN
   -- SQL statements to be executed after DELETE operation
END;

Here, "trigger_name" is the name of the trigger to be created, "table_name" is the name of the table on which the trigger will be created, and the "FOR EACH ROW" clause is used to specify that the trigger is executed for each deleted row.

Example

Let's say we have a table called "employees" and we want to create an AFTER DELETE trigger that inserts data into a log table every time a row is deleted from the "employees" table. Here's how we can implement it:

CREATE TRIGGER employee_deleted
AFTER DELETE
ON employees
FOR EACH ROW
BEGIN
   INSERT INTO employees_log (id, name, action) VALUES (OLD.id, OLD.name, 'deleted');
END;

In this example, we create an AFTER DELETE trigger called "employee_deleted" that is executed after every row is deleted from the "employees" table. The trigger inserts data into a log table called "employees_log", which contains the id, name, and action of the deleted employee.

Output/Explanation

Once the trigger is created, it automatically executes every time a row is deleted from the "employees" table. In this case, the trigger inserts data into the "employees_log" table every time a row is deleted, recording the information of the deleted employee.

Use

The main use of an AFTER DELETE trigger in MySQL is to perform some action automatically after a row has been deleted from a table. This can be useful for logging purposes, auditing, or for implementing some additional business logic.

Important Points

• An AFTER DELETE trigger is executed automatically after a row has been deleted from a table.
• The "FOR EACH ROW" clause specifies that the trigger is executed once for each deleted row.
• Triggers can be created for any table in the database except for views.

Summary

In this tutorial, we discussed how to create an AFTER DELETE trigger in MySQL. We covered the syntax, an example of how to use the trigger, its output/explanation, use cases, and some important points to keep in mind when creating triggers in MySQL. With this knowledge, you can now use AFTER DELETE triggers to automate actions and perform additional business logic after a row has been deleted from a table in MySQL.