DELETE Triggers and Their Use Cases in MySQL

Course: MySQL Training

Topic: DELETE Triggers and Their Use Cases

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

Official Reference: MySQL Trigger Syntax and Examples

1. Introduction to DELETE Triggers

A DELETE trigger in MySQL is a database object that automatically executes when a DELETE operation removes rows from an associated table. DELETE triggers help developers maintain audit records, preserve information about deleted data, enforce business rules, and perform related database operations.

For example, when an employee leaves an organization, a DELETE trigger can record the employee's details in an audit table before the original employee record is removed. This helps administrators understand which record was deleted and when the deletion occurred.

MySQL supports two trigger timings for DELETE operations:

• BEFORE DELETE: Executes before the row is deleted. It is useful for checking deletion conditions and recording information about the row that is about to be removed.
• AFTER DELETE: Executes after the row has been successfully deleted. It is useful for logging completed deletions and performing permitted follow-up operations.

2. Learning Objectives

After completing this topic, you will be able to:

• Understand DELETE triggers and their purpose in MySQL.
• Differentiate BEFORE DELETE and AFTER DELETE triggers.
• Understand how the OLD keyword works in DELETE triggers.
• Create a BEFORE DELETE trigger to record information before deletion.
• Create an AFTER DELETE trigger to maintain an audit log.
• Apply DELETE triggers to employee, product, order, and customer databases.
• Understand trigger execution, errors, and transactional behavior.
• Inspect, test, troubleshoot, and remove DELETE triggers.
• Identify the limitations and best practices associated with DELETE triggers.

3. What Is a DELETE Trigger?

A DELETE trigger is a stored program associated with a database table. It runs automatically when a DELETE statement removes a row from that table.

Unlike a stored procedure, which is usually executed by calling it explicitly, a trigger is activated by its associated database event. Developers do not need to call a DELETE trigger manually.

3.1 Example Scenario

Consider an employee management system with an employees table. When an administrator deletes an employee, the organization may want to retain the employee's ID, name, department, and salary for auditing purposes.

A DELETE trigger can copy the employee's existing information into an employee_delete_log table before the original row is removed.

DELETE FROM employees
WHERE employee_id = 101;

If a suitable trigger exists, MySQL automatically executes it as part of the DELETE operation.

3.2 Common Uses of DELETE Triggers

4. Understanding BEFORE DELETE Triggers

A BEFORE DELETE trigger executes before MySQL deletes a row from the associated table. At this point, the row still exists, and its current values can be accessed through the OLD keyword.

BEFORE DELETE triggers are useful when a database needs to inspect the row before removal, preserve its values, or prevent a deletion when a business rule is violated.

4.1 How a BEFORE DELETE Trigger Works

1. A user or application executes a DELETE statement.
2. MySQL identifies the rows affected by the statement.
3. The BEFORE DELETE trigger executes for each affected row.
4. The trigger can read the existing row's values through OLD.column_name.
5. The trigger may record information in another table or raise an error to reject the deletion.
6. If the trigger and applicable database operations succeed, MySQL proceeds with deleting the row.
7. For successful row deletions, any applicable AFTER DELETE trigger executes afterward.

4.2 Basic Syntax

CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name
FOR EACH ROW
trigger_body;

5. Understanding AFTER DELETE Triggers

An AFTER DELETE trigger executes after a row has been successfully deleted from the associated table. It is commonly used to record completed deletions or perform related database operations.

Because the original row has already been removed from the table, an AFTER DELETE trigger cannot retrieve that row by querying the original table as if it were still present. However, the trigger can access the deleted row's previous values through OLD.column_name.

5.1 How an AFTER DELETE Trigger Works

1. A DELETE statement identifies one or more rows.
2. Any applicable BEFORE DELETE trigger executes for each row.
3. MySQL deletes a row if the operation is allowed and succeeds.
4. The AFTER DELETE trigger executes for that successfully deleted row.
5. The trigger can use OLD.column_name to access the deleted row's previous values.
6. The trigger can insert those values into an audit table or perform another permitted database operation.

5.2 Basic Syntax

CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
FOR EACH ROW
trigger_body;

An AFTER DELETE trigger cannot cancel the deletion by raising an error and thereby restore the row automatically. An error can cause the statement to fail, but rollback behavior depends on the storage engine and transaction context. For InnoDB, changes made by a failed statement are normally rolled back as part of statement failure.

6. Difference Between BEFORE DELETE and AFTER DELETE

7. Understanding the OLD Keyword

The OLD keyword represents the existing values of a row being deleted. It allows a DELETE trigger to read column values even when the row is about to be removed or has already been removed by the time an AFTER DELETE trigger executes.

7.1 Example

OLD.employee_id
OLD.employee_name
OLD.department
OLD.salary

Suppose the following statement is executed:

DELETE FROM employees
WHERE employee_id = 101;

Inside the DELETE trigger, the following values might be available:

• OLD.employee_id contains 101.
• OLD.employee_name contains the employee's existing name.
• OLD.department contains the employee's existing department.
• OLD.salary contains the employee's existing salary.

7.2 OLD and NEW Comparison

DELETE triggers use OLD because they operate on existing rows that are being removed. There is no NEW row for a DELETE operation.

8. Preparing the Database for DELETE Trigger Examples

The following examples use an employee management database. Create the database and tables before creating the triggers.

8.1 Create a Database

CREATE DATABASE mysql_delete_trigger_demo;
USE mysql_delete_trigger_demo;

If you already have a suitable database, select it with USE instead of creating a new database.

8.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

Column descriptions:

• employee_id uniquely identifies each employee.
• employee_name stores the employee's name.
• department stores the department name.
• salary stores the employee's salary.
• created_at records the row creation timestamp.

8.3 Insert Sample Data

INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(101, 'Amit Sharma', 'IT', 55000.00),
(102, 'Priya Mehta', 'HR', 48000.00),
(103, 'Rahul Verma', 'Finance', 62000.00),
(104, 'Neha Singh', 'Marketing', 51000.00);

Verify the inserted records:

SELECT * FROM employees;

9. Creating a BEFORE DELETE Trigger to Record Deleted Employee Details

Suppose an organization wants to preserve employee details before deleting an employee record. A BEFORE DELETE trigger can insert the existing employee information into a separate log table before the original row is removed.

9.1 Create an Employee Deletion Log Table

CREATE TABLE employee_delete_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    deletion_recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

This table stores employee details captured during deletion. The log table does not use a foreign key to the employees table because the corresponding employee row is expected to be removed.

9.2 Create the BEFORE DELETE Trigger

DELIMITER //

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_delete_log (
        employee_id,
        employee_name,
        department,
        salary
    )
    VALUES (
        OLD.employee_id,
        OLD.employee_name,
        OLD.department,
        OLD.salary
    );
END//

DELIMITER ;

9.3 Explanation of the Trigger

• CREATE TRIGGER before_employee_delete creates a trigger named before_employee_delete.
• BEFORE DELETE ON employees associates the trigger with DELETE operations on employees.
• FOR EACH ROW means the trigger executes for each row affected by the DELETE statement.
• OLD.employee_id reads the existing employee identifier.
• OLD.employee_name reads the existing employee name.
• OLD.department reads the existing department.
• OLD.salary reads the existing salary.
• The INSERT statement stores those values in employee_delete_log.

9.4 Test the BEFORE DELETE Trigger

DELETE FROM employees
WHERE employee_id = 102;

SELECT * FROM employee_delete_log;

SELECT * FROM employees
WHERE employee_id = 102;

Expected behavior:

• The employee's information is recorded in employee_delete_log.
• The employee row is removed from employees if the deletion succeeds.
• The final SELECT against employees returns no row for employee_id 102.

Important: A BEFORE DELETE trigger records information before the deletion itself completes. With transactional tables, if the statement later fails, the audit insertion made by the trigger is normally rolled back along with the statement.

10. Creating a BEFORE DELETE Trigger to Prevent Deletion

Some records must be protected from deletion. For example, an organization might require that an employee marked as a department administrator cannot be deleted until the administrator role is reassigned.

One approach is to store a protection flag in the employees table.

10.1 Create a Table with a Protection Flag

CREATE TABLE protected_employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    is_protected BOOLEAN NOT NULL DEFAULT FALSE
) ENGINE = InnoDB;

INSERT INTO protected_employees
(employee_id, employee_name, is_protected)
VALUES
(201, 'Karan Patel', TRUE),
(202, 'Sneha Rao', FALSE);

10.2 Create the Trigger

DELIMITER //

CREATE TRIGGER before_protected_employee_delete
BEFORE DELETE ON protected_employees
FOR EACH ROW
BEGIN
    IF OLD.is_protected = TRUE THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'This employee record is protected from deletion';
    END IF;
END//

DELIMITER ;

10.3 Test Deletion of a Protected Employee

DELETE FROM protected_employees
WHERE employee_id = 201;

MySQL raises the custom error, and the protected row is not deleted.

10.4 Test Deletion of an Unprotected Employee

DELETE FROM protected_employees
WHERE employee_id = 202;

The trigger allows the deletion because the employee is not marked as protected, provided no other database rule prevents it.

Design note: In a production system, permissions, foreign keys, application-level authorization, and database constraints should also be considered. A trigger is one layer of protection, not a replacement for a complete access-control strategy.

11. Creating an AFTER DELETE Trigger for Audit Logging

An AFTER DELETE trigger is appropriate when an audit record should represent a row that was successfully deleted. The trigger can use OLD values to save details about the removed row.

11.1 Create an Audit Table

CREATE TABLE employee_deletion_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    action_type VARCHAR(20) NOT NULL,
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

11.2 Create the AFTER DELETE Trigger

DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_deletion_audit (
        employee_id,
        employee_name,
        department,
        salary,
        action_type
    )
    VALUES (
        OLD.employee_id,
        OLD.employee_name,
        OLD.department,
        OLD.salary,
        'DELETE'
    );
END//

DELIMITER ;

11.3 Explanation

• AFTER DELETE ON employees specifies that the trigger runs after a successful row deletion.
• OLD.employee_id provides the deleted employee's identifier.
• OLD.employee_name provides the deleted employee's name.
• OLD.department provides the deleted employee's department.
• OLD.salary provides the deleted employee's salary.
• 'DELETE' identifies the recorded action.
• The INSERT statement saves the details in employee_deletion_audit.

11.4 Test the AFTER DELETE Trigger

DELETE FROM employees
WHERE employee_id = 103;

SELECT * FROM employee_deletion_audit;

SELECT * FROM employees
WHERE employee_id = 103;

If the deletion succeeds, the employee's information should appear in employee_deletion_audit, while the original row should no longer exist in employees.

12. Using BEFORE DELETE and AFTER DELETE Together

Both trigger types can be useful in the same database design. The BEFORE DELETE trigger can enforce a rule or capture pre-deletion information, while the AFTER DELETE trigger can record a completed deletion.

12.1 Example Workflow

1. An administrator requests deletion of an employee.
2. The BEFORE DELETE trigger checks the employee's status or protection flag.
3. If the employee is protected, the trigger raises an error and the deletion does not proceed.
4. If the deletion is permitted, MySQL removes the employee row.
5. The AFTER DELETE trigger records the deleted employee's details in an audit table.
6. The application can query the audit table to review the deletion.

12.2 Example DELETE Statement

DELETE FROM employees
WHERE employee_id = 104;

SELECT * FROM employee_delete_log
WHERE employee_id = 104;

SELECT * FROM employee_deletion_audit
WHERE employee_id = 104;

If both the BEFORE DELETE logging trigger and AFTER DELETE audit trigger are defined on employees, a successful deletion can create a record in each log table. This is useful for learning, but production systems should avoid storing duplicate information unnecessarily.

13. DELETE Triggers and Multiple-Row Deletions

MySQL triggers are row-level triggers. When a DELETE statement affects several rows, the applicable trigger executes separately for each row being processed.

13.1 Example

DELETE FROM employees
WHERE department = 'IT';

If three rows match the condition, the BEFORE DELETE trigger executes for each affected row, and the AFTER DELETE trigger executes for each row successfully deleted.

13.2 Verify the Audit Results

SELECT employee_id, employee_name, department
FROM employee_delete_log
ORDER BY log_id;

SELECT employee_id, employee_name, department, action_type
FROM employee_deletion_audit
ORDER BY audit_id;

Always inspect the WHERE condition before running a DELETE statement. If the condition is omitted, the statement attempts to delete all rows from the table, subject to triggers and database constraints.

14. DELETE Triggers with WHERE Conditions

The WHERE clause determines which rows are affected by a DELETE statement. A DELETE trigger executes only for the rows the operation processes, not for every row in the table.

14.1 Delete One Employee

DELETE FROM employees
WHERE employee_id = 101;

The trigger executes for the employee with ID 101 if the row exists and the operation proceeds.

14.2 Delete Employees from a Specific Department

DELETE FROM employees
WHERE department = 'Marketing';

The trigger executes for each employee row deleted from the Marketing department.

14.3 Delete Employees Below a Salary Threshold

DELETE FROM employees
WHERE salary < 30000;

This statement attempts to delete rows whose salary is below 30,000. In a real system, verify that the business requirement truly calls for permanent deletion rather than an employee-status update.

15. Practical Use Cases of DELETE Triggers

15.1 Employee Deletion Audit

Organizations can retain selected employee details when an employee record is removed. An audit record can help answer who or what was deleted and when the deletion was recorded.

15.2 Product Removal History

An online store may record a product's ID, name, category, and price when a product is removed from its product catalogue.

15.3 Customer Account Deletion

A customer management system may record limited deletion metadata to support operational auditing. Sensitive personal information should be retained only when there is a valid purpose and legal basis, and according to the organization's retention policy.

15.4 Preventing Deletion of Important Records

A BEFORE DELETE trigger can reject deletion of records that meet a protected condition, such as a record marked as legally restricted. The protection rules should be documented and tested.

15.5 Maintaining Summary Data

A trigger may update a summary table after a detail record is deleted. However, the logic must be designed carefully to avoid inconsistent totals or unnecessary performance overhead.

15.6 Inventory Audit

When an inventory record is removed, a trigger can record the product ID, quantity, and removal time. For inventory systems, it is often safer to record stock movements or mark records inactive instead of deleting important transaction history.

16. Example: Creating a Product Deletion Audit Trigger

This example demonstrates how an online store can record a product's details when the product is deleted.

16.1 Create a Products Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50)
) ENGINE = InnoDB;

16.2 Insert Sample Data

INSERT INTO products
(product_id, product_name, price, category)
VALUES
(1, 'Wireless Mouse', 799.00, 'Accessories'),
(2, 'Laptop Stand', 899.00, 'Accessories'),
(3, 'Webcam', 1599.00, 'Electronics');

16.3 Create the Product Audit Table

CREATE TABLE product_delete_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

16.4 Create the Trigger

DELIMITER //

CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_delete_audit (
        product_id,
        product_name,
        price,
        category
    )
    VALUES (
        OLD.product_id,
        OLD.product_name,
        OLD.price,
        OLD.category
    );
END//

DELIMITER ;

16.5 Delete a Product and Verify

DELETE FROM products
WHERE product_id = 2;

SELECT * FROM product_delete_audit;

SELECT * FROM products;

The product with ID 2 should be absent from products after a successful deletion. Its previous details should be present in product_delete_audit.

17. Trigger Errors and Transaction Behavior

A trigger executes as part of the statement that activates it. If a trigger raises an error or one of its SQL statements fails, the DELETE statement can fail as well.

17.1 Error in a BEFORE DELETE Trigger

If a BEFORE DELETE trigger raises an error, the corresponding row deletion does not proceed. For transactional tables, changes made by the failed statement are rolled back.

17.2 Error in an AFTER DELETE Trigger

If an AFTER DELETE trigger fails, the DELETE statement fails. With InnoDB tables, the deletion and related transactional changes made by the statement are normally rolled back.

17.3 Example of a Trigger Error

DELIMITER //

CREATE TRIGGER before_employee_delete_check
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.department = 'Management' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Management employee records cannot be deleted';
    END IF;
END//

DELIMITER ;

If a row belonging to the Management department is targeted, the trigger raises an error and blocks the deletion. Rows in other departments can be deleted if no other rules prevent it.

Note: This is an additional example trigger. If another BEFORE DELETE trigger already exists on employees, multiple triggers may coexist, but their execution order and combined behavior should be reviewed.

18. Important Rules and Limitations

• DELETE triggers are associated with a specific table and execute automatically when matching DELETE operations occur.
• Use OLD.column_name to access the row's previous values.
• NEW is not available as a new-row reference in a DELETE trigger.
• OLD values are read-only and cannot be modified using SET OLD.column_name.
• MySQL triggers use FOR EACH ROW rather than statement-level trigger execution.
• Multiple-row DELETE statements can activate a trigger once for each affected row.
• Trigger errors can cause the statement that activated the trigger to fail.
• Transactional behavior depends on the storage engine and transaction context; InnoDB provides transactional rollback.
• Triggers cannot directly execute transaction-control statements such as COMMIT or ROLLBACK.
• Foreign key actions and other database constraints can affect whether a DELETE operation succeeds.
• DELETE triggers do not automatically run for every kind of operation that removes data. For example, TRUNCATE TABLE is not a row-by-row DELETE operation and does not activate DELETE triggers.
• Choose audit retention policies carefully, especially when logs contain personal or confidential information.

19. DELETE Triggers vs Foreign Key ON DELETE Actions

DELETE triggers and foreign key actions can both affect related data, but they serve different purposes.

Use foreign key constraints to maintain referential integrity. Use DELETE triggers for additional logic that is not adequately expressed through constraints alone.

20. DELETE vs TRUNCATE vs DROP

These operations are not interchangeable. Use DELETE when row-level trigger behavior is required. TRUNCATE and DROP also have different transactional and structural consequences, so they should be used carefully.

21. Viewing and Managing DELETE Triggers

21.1 Show All Triggers

SHOW TRIGGERS;

This displays triggers associated with the currently selected database.

21.2 Show Triggers in a Specific Database

SHOW TRIGGERS FROM mysql_delete_trigger_demo;

21.3 Inspect a Trigger Definition

SHOW CREATE TRIGGER before_employee_delete;

This statement displays the stored SQL definition of the specified trigger.

21.4 Remove a Trigger

DROP TRIGGER IF EXISTS before_employee_delete;

Dropping a trigger removes its definition. It does not delete existing audit records or remove the associated table.

22. Common Mistakes and Troubleshooting

23. Best Practices for DELETE Triggers

23.1 Keep Trigger Logic Simple

Use triggers for focused operations such as recording deletion metadata, validating deletion conditions, or maintaining a small related table. Complex business workflows are often easier to test and maintain in application code or stored procedures.

23.2 Use Transactional Tables

Use InnoDB for the source and audit tables when deletion and audit recording must succeed or fail together. Verify that all related operations participate in the expected transaction.

23.3 Avoid Unnecessary Duplicate Logs

If both BEFORE DELETE and AFTER DELETE triggers write to separate audit tables, determine whether both records are necessary. Duplicate logs can increase storage requirements and make auditing more complicated.

23.4 Protect Sensitive Data

Audit tables may contain personal, financial, or confidential information. Limit access, avoid storing unnecessary fields, and define retention and deletion policies that comply with applicable requirements.

23.5 Use WHERE Clauses Carefully

Before executing a DELETE statement, use a SELECT statement with the same WHERE condition to review the target rows.

SELECT *
FROM employees
WHERE department = 'Marketing';

DELETE FROM employees
WHERE department = 'Marketing';

23.6 Test Failure Scenarios

Test permitted deletions, rejected deletions, multi-row deletions, audit-table failures, foreign key restrictions, and transaction rollback behavior.

23.7 Document Trigger Dependencies

Document each trigger's purpose, table, timing, conditions, related tables, and expected error behavior. This helps developers troubleshoot unexpected deletions and audit results.

24. Practical Mini Project: Employee Deletion Audit System

Project Objective: Create a simple employee management database that records deleted employee information and blocks deletion of protected employee records.

24.1 Project Requirements

• Create an employees table.
• Create an employee_delete_log table.
• Create an employee_deletion_audit table.
• Create a BEFORE DELETE trigger to preserve employee details before deletion.
• Create an AFTER DELETE trigger to record successful deletions.
• Create a BEFORE DELETE validation trigger for protected employees.
• Test valid and blocked deletion operations.
• Verify the employee table and audit tables.

24.2 Insert Sample Records

INSERT INTO employees
(employee_id, employee_name, department, salary)
VALUES
(301, 'Aarav Shah', 'IT', 60000.00),
(302, 'Isha Kapoor', 'HR', 52000.00),
(303, 'Dev Malhotra', 'Finance', 65000.00);

24.3 Delete a Selected Employee

DELETE FROM employees
WHERE employee_id = 301;

24.4 Verify the Source and Audit Tables

SELECT * FROM employees
WHERE employee_id = 301;

SELECT * FROM employee_delete_log
WHERE employee_id = 301;

SELECT * FROM employee_deletion_audit
WHERE employee_id = 301;

24.5 Expected Learning Outcome

The deleted employee should no longer appear in employees after a successful deletion. If the relevant triggers are configured, the employee's information should be available in the corresponding log and audit tables.

25. Practice Exercises

1. Create a BEFORE DELETE trigger that stores deleted product IDs and names in a product history table.
2. Create an AFTER DELETE trigger that records the old price of a deleted product.
3. Create a BEFORE DELETE trigger that prevents deletion when an employee belongs to the Management department.
4. Delete multiple employees from a selected department and verify the audit records.
5. Test the difference between DELETE and TRUNCATE TABLE with respect to DELETE triggers.
6. Use SHOW TRIGGERS to list triggers in the current database.
7. Use SHOW CREATE TRIGGER to inspect a trigger definition.
8. Drop a test trigger with DROP TRIGGER IF EXISTS.
9. Test how an error in an audit-table insertion affects a DELETE statement using InnoDB tables.
10. Explain why OLD is used in DELETE triggers rather than NEW.

26. Practice Exercise Solutions

26.1 Create a Product History Table

CREATE TABLE product_delete_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

26.2 Create a BEFORE DELETE Trigger for Product History

DELIMITER //

CREATE TRIGGER before_product_delete_history
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_delete_history (
        product_id,
        product_name
    )
    VALUES (
        OLD.product_id,
        OLD.product_name
    );
END//

DELIMITER ;

This trigger assumes the products table contains product_id and product_name columns, as in the example in Section 16. If your table uses a different structure, adjust the column references accordingly.

26.3 Block Deletion of Management Employees

DELIMITER //

CREATE TRIGGER before_management_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.department = 'Management' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Management employee records cannot be deleted';
    END IF;
END//

DELIMITER ;

This trigger blocks deletion of employees whose department value is Management. If another BEFORE DELETE trigger already exists, check the trigger execution order and combined behavior.

27. MySQL DELETE Trigger Interview Questions and Answers

Q1. What is a DELETE trigger in MySQL?

A DELETE trigger is a database object that executes automatically when a DELETE operation affects rows in its associated table.

Q2. What is a BEFORE DELETE trigger?

It executes before a row is removed and can inspect existing values, record information, or reject the deletion by raising an error.

Q3. What is an AFTER DELETE trigger?

It executes after a row has been successfully deleted and can record the deleted row's previous values or perform permitted follow-up operations.

Q4. What is the difference between BEFORE DELETE and AFTER DELETE?

BEFORE DELETE runs before the row removal. AFTER DELETE runs after the row operation succeeds.

Q5. What does OLD mean in a DELETE trigger?

OLD provides access to the values of the row being deleted, including its column values.

Q6. Can a DELETE trigger use NEW?

No new row exists for a DELETE operation. Use OLD to access the deleted row's values.

Q7. Can a DELETE trigger prevent deletion?

A BEFORE DELETE trigger can raise an error using SIGNAL when a business rule prohibits the operation.

Q8. Can a DELETE trigger log deleted records?

Yes. It can insert selected OLD values into a separate audit or history table.

Q9. Does TRUNCATE TABLE activate DELETE triggers?

No. TRUNCATE TABLE does not delete rows one by one and does not activate row-level DELETE triggers.

Q10. What happens if an AFTER DELETE trigger fails?

The DELETE statement fails. With InnoDB tables, changes made by the failed statement are normally rolled back.

Q11. Can multiple DELETE triggers exist on one table?

Yes. MySQL permits multiple triggers for the same table, event, and timing. Their execution order can be managed with FOLLOWS or PRECEDES where appropriate.

Q12. How do you display triggers?

Use SHOW TRIGGERS; in the selected database.

Q13. How do you inspect a trigger's definition?

Use SHOW CREATE TRIGGER trigger_name;.

Q14. How do you delete a trigger definition?

Use DROP TRIGGER IF EXISTS trigger_name;.

Q15. Why are DELETE triggers useful for auditing?

They can preserve selected information about rows that have been deleted, helping administrators investigate and review database changes.

28. Quick Revision Notes

• DELETE trigger: Executes automatically when a DELETE operation processes rows in a table.
• BEFORE DELETE: Runs before a row is removed.
• AFTER DELETE: Runs after a row is successfully removed.
• OLD: Provides access to the previous row values.
• FOR EACH ROW: Executes the trigger body for every affected row.
• SIGNAL SQLSTATE '45000': Raises a user-defined error.
• Audit table: Stores selected information about database actions.
• SHOW TRIGGERS: Lists triggers in the selected database.
• SHOW CREATE TRIGGER: Displays a trigger's definition.
• DROP TRIGGER: Removes a trigger definition.
• TRUNCATE TABLE: Does not activate row-level DELETE triggers.
• InnoDB: Supports transactional rollback when a statement fails.

29. Conclusion

DELETE triggers are useful MySQL features for managing operations that remove data. BEFORE DELETE triggers can validate deletion conditions and capture information before a row is removed. AFTER DELETE triggers can record completed deletions and perform permitted follow-up database operations.

By practicing OLD column references, audit logging, validation with SIGNAL, multi-row deletions, and trigger management, you can build more reliable database workflows. Use triggers carefully, test failure scenarios, protect sensitive audit information, and choose the appropriate database constraints for maintaining data integrity.

Continue Learning: Explore more database concepts through the MySQL Training Course. You can also visit the Course Demo Registration Page to learn more about the training.