MySQL AFTER DELETE Trigger

The MySQL can create an AFTER DELETE trigger to automatically execute a set of SQL statements after a row or rows have been deleted from a table. These triggers allow you to implement custom logic or record actions taken after a deletion has occurred. Here’s the basic syntax for creating an AFTER DELETE trigger:

DELIMITER //
CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;
//
DELIMITER ;

Here’s an explanation of each part of the trigger definition:

• trigger_name: Choose a unique name for your trigger.
• AFTER DELETE: This specifies that the trigger will be executed after a DELETE operation on the table.
• table_name: Replace this with the name of the table on which the trigger should be defined.
• FOR EACH ROW: This indicates that the trigger will be executed for each row affected by the DELETE operation.
• BEGIN and END: These enclose the trigger logic, which consists of one or more SQL statements.

Within the trigger logic, you can reference the old values of the row being deleted using the OLD keyword. For example, you can log the deleted data, perform cleanup operations, or record the action in an audit log.

Here’s an example of an AFTER DELETE trigger that logs deleted records in a hypothetical audit_log table:

DELIMITER //
CREATE TRIGGER log_deleted_orders
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, deleted_id, deleted_data, deleted_at)
    VALUES ('DELETE', 'orders', OLD.order_id, OLD.order_data, NOW());
END;
//
DELIMITER ;

In the above trigger, after a row is deleted from the orders table, the trigger records the action, table name, the ID of the deleted record, the deleted data, and the timestamp in an audit_log table.

AFTER DELETE triggers are often used for audit logging, data archiving, and other post-deletion actions. Always test your triggers thoroughly to ensure they behave as expected in your specific use case.