Introduction
In this chapter, we will learn about the AFTER DELETE
trigger in MySQL. An AFTER DELETE
trigger is a type of trigger that is executed automatically after a DELETE
operation is performed on a table. This type of trigger is useful for tasks such as logging deletions, maintaining audit trails, or synchronizing data across tables. We will cover the syntax for creating AFTER DELETE
triggers, examples of their usage, and important considerations for using them in MySQL.
Syntax
The basic syntax for creating an AFTER DELETE
trigger in MySQL is:
CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
-- Trigger body: SQL statements
END;
trigger_name
: The name of the trigger.table_name
: The name of the table on which the trigger is defined.
Example
Here is an example of how to create an AFTER DELETE
trigger that logs deletions from the employees
table:
- Create a Database
CREATE DATABASE company;
- Select the Database
USE company;
- Create the Employees and Audit Tables
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE,
salary DECIMAL(10, 2)
);
CREATE TABLE employee_audit (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
action VARCHAR(10),
action_time DATETIME
);
- Create the AFTER DELETE Trigger
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, first_name, last_name, action, action_time)
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, 'delete', NOW());
END;
In this example, the after_employee_delete
trigger logs any deletions from the employees
table into the employee_audit
table after the delete operation occurs.
Full Example
Let’s go through a complete example where we create a database, table, and trigger, and then demonstrate the usage of the AFTER DELETE
trigger.
- Create a Database
CREATE DATABASE company;
- Select the Database
USE company;
- Create the Employees and Audit Tables
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE,
salary DECIMAL(10, 2)
);
CREATE TABLE employee_audit (
audit_id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
action VARCHAR(10),
action_time DATETIME
);
- Create the AFTER DELETE Trigger
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, first_name, last_name, action, action_time)
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, 'delete', NOW());
END;
- Insert Data into the Employees Table
INSERT INTO employees (first_name, last_name, hire_date, salary) VALUES
('Rahul', 'Sharma', '2023-07-05', 50000.00),
('Priya', 'Singh', '2023-07-05', 60000.00),
('Amit', 'Kumar', '2023-07-05', 55000.00);
- Delete an Employee Record
DELETE FROM employees WHERE employee_id = 2;
- Query the Employee Audit Table
SELECT * FROM employee_audit;
Output
audit_id | employee_id | first_name | last_name | action | action_time |
---|---|---|---|---|---|
1 | 2 | Priya | Singh | delete | 2023-07-05 10:15:00 |
In this example, the after_employee_delete
trigger logs deletions from the employees
table, recording the employee_id
, first name, last name, action type (delete
), and the current timestamp.
Using AFTER DELETE Trigger for Data Synchronization
You can also use an AFTER DELETE
trigger to synchronize data across tables.
Example: Synchronize Data with Another Table
CREATE TABLE employees_archive (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
salary DECIMAL(10, 2),
deleted_at DATETIME
);
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_archive (employee_id, first_name, last_name, hire_date, salary, deleted_at)
VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.hire_date, OLD.salary, NOW());
END;
In this example, the after_employee_delete
trigger ensures that any employee record deleted from the employees
table is also added to the employees_archive
table with the deletion timestamp.
Important Considerations
- Performance: Triggers can affect performance, especially if they contain complex logic or if they fire frequently. Use them judiciously.
- Error Handling: Use the
SIGNAL
statement to raise custom errors within triggers for data validation and business rule enforcement. - Testing: Thoroughly test triggers to ensure they perform as expected and do not introduce unwanted side effects.
- Data Integrity: Triggers can help maintain data integrity by ensuring certain actions are taken automatically after data is deleted.
Conclusion
The AFTER DELETE
trigger in MySQL is used for automating tasks and enforcing business rules after data is deleted from a table. This chapter covered the syntax for creating AFTER DELETE
triggers, provided examples of their usage, and discussed important considerations.