MySQL AFTER DELETE Trigger

In MySQL, triggers are a concept where SQL developers can define automatic actions to occur in response to specific changes in a database table. An AFTER DELETE trigger, for instance, is invoked automatically after a row is deleted from a table, allowing developers to perform additional operations such as logging, data cleanup, or cascading updates.

In this article, We will learn about the MySQL AFTER DELETE Trigger by understanding various examples and so on.

MySQL AFTER DELETE Trigger

• A MySQL AFTER DELETE trigger is a type of database trigger that activates automatically after a row is deleted from a specified table.
  
• This trigger can be used to perform additional operations, such as logging the deletion, updating related tables, or enforcing business rules, making sure that all required actions occur in response to the delete operation.

Syntax:

The syntax for using AFTER DELETE Trigger in MySQL is as follows:

CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger actions (e.g., INSERT INTO another_table, UPDATE table_name, etc.)
END;

Parameters

• trigger_name: The name of the trigger.
• AFTER DELETE ON table_name: Specifies that the trigger should activate after a delete operation on the specified table.
• FOR EACH ROW: Indicates that the trigger will be executed once for each row affected by the delete operation.
• BEGIN ... END;: Contains the SQL statements that define the actions to be performed when the trigger is activated.

Example of MySQL AFTER DELETE Trigger

Let’s look at example of the AFTER DELETE Trigger in MySQL. Learning the AFTER DELETE Trigger with examples will help in understanding the concept better.

First, let’s create a table:

We create the table "employees" in this example.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100) NOT NULL
);

DESCRIBE employees ;

Output

Example: Creating AFTER DELETE Trigger that Performs Logging Delete Operation

In this example, we are creating an AFTER DELETE trigger named after_employee_delete that logs deleted records into the deletion_log table. First, we set up the deletion_log table to track deletions, then insert sample data into the employees table. The trigger is defined to insert details of deleted records into deletion_log when a delete operation occurs. Finally, deleting a record from employees triggers this action, and we verify the log entry by querying the deletion_log table.

Step 1: Firstly we need to create a log table to track the after delete operations.

CREATE TABLE deletion_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action VARCHAR(50)
);

DESCRIBE deletion_log;

Step 2: Insert some sample data into the employees table:

INSERT INTO employees (name, position) VALUES ('Gaurav', 'Software Engineer');
INSERT INTO employees (name, position) VALUES ('Yuvraj', 'Project Manager');

Step 3: Now, create a trigger that logs deleted records into the deletion_log table:

DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deletion_log (employee_id, old_name, old_position)
    VALUES (OLD.id, OLD.name, OLD.position);
END//

DELIMITER ;

Step 4: Delete a record from the employees table to see the trigger in action. Verify that the deleted record has been logged:

DELETE FROM employees WHERE id = 1;
SELECT * FROM deletion_log;

Output:

Conclusion

In conclusion, an AFTER DELETE trigger is useful for automating post-deletion operations, such as logging deleted records for audit purposes or further processing. By setting up this trigger, you make sure that every delete action is tracked and relevant information is recorded, enhancing data integrity.