MySQL - After Delete Trigger

MySQL - AFTER DELETE Triggers

In MySQL, a trigger is a special type of stored program that is automatically executed, or ‘fired’, in response to certain events on a particular table. These events are INSERT, UPDATE, or DELETE operations. Triggers are often used to maintain data integrity, enforce complex business rules, or create audit logs.

Triggers can be set to fire BEFORE or AFTER the data-modifying event occurs.

The AFTER DELETE Trigger

An AFTER DELETE trigger is a row-level trigger that executes automatically for each row that is deleted from a table. Because it fires after the deletion, you cannot modify the row being deleted (it’s already gone), but you can access its original values to perform other actions, like logging the deletion to an archive table.

Inside a DELETE trigger, you can use the OLD keyword to access the values of the columns in the row that was just deleted. For example, OLD.ID refers to the value of the ID column of the deleted row.

Syntax

CREATE TRIGGER trigger_name
    AFTER DELETE ON table_name
    FOR EACH ROW
BEGIN
    -- Trigger body: one or more SQL statements
    -- Use OLD.column_name to access values from the deleted row.
END;

Practical Example: Creating an Audit Trail

A common use for an AFTER DELETE trigger is to maintain an audit trail. When a record is deleted from a primary table, we can automatically insert a copy of that record into an archive or log table. This ensures no data is ever truly lost.

Step 1: Create the Main and Archive Tables

First, let’s create our main CUSTOMERS table and an OLD_CUSTOMERS table to store the deleted records. The structure should be identical.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2)
);

INSERT INTO CUSTOMERS(NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', 2000.00),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', 23, 'Kota', 2000.00);

CREATE TABLE OLD_CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    AGE INT NOT NULL,
    ADDRESS VARCHAR(100),
    SALARY DECIMAL(18, 2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the AFTER DELETE Trigger

Now, create the trigger. It will fire after a DELETE on CUSTOMERS and insert the OLD row data into OLD_CUSTOMERS.

DELIMITER //

CREATE TRIGGER trg_customer_archive
AFTER DELETE ON CUSTOMERS
FOR EACH ROW
BEGIN
    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
END //

DELIMITER ;

Note: DELIMITER // changes the statement delimiter from ; to //, allowing the trigger’s BEGIN...END block (which contains semicolons) to be parsed as a single statement.

Verifying the Trigger

Let’s delete a customer and see if our trigger works.

DELETE FROM CUSTOMERS WHERE ID = 3;

Now, check the contents of both tables.

SELECT * FROM CUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00

SELECT * FROM OLD_CUSTOMERS;

ID	NAME	AGE	ADDRESS	SALARY	deleted_at
3	Kaushik	23	Kota	2000.00	YYYY-MM-DD HH:MM:SS

Success! The record for ‘Kaushik’ was removed from CUSTOMERS and an identical copy was inserted into OLD_CUSTOMERS, effectively archiving the deletion.

Considerations and Best Practices

• Performance: Triggers add overhead to DML operations. Keep trigger logic as simple and efficient as possible.
• Complexity: Triggers execute implicitly (‘magic’ behavior), which can make application logic harder to debug and reason about. For complex business rules, it’s often better to handle the logic in your application code or a stored procedure.
• Alternatives: Before using a trigger, consider if the task can be handled by your application. The trade-off is between guaranteed execution in the database (trigger) versus more maintainable and testable code in your application.
• Cascading Effects: Be cautious of triggers that cause other triggers to fire. This can lead to complex and hard-to-predict behavior.

Managing Triggers with Client Code

You can create, check, and drop triggers programmatically as part of your application’s deployment or migration scripts.

PHP
NodeJS
Java
Python



// Assumes a connected $mysqli object
$triggerSql = "
    CREATE TRIGGER trg_customer_archive
    AFTER DELETE ON CUSTOMERS
    FOR EACH ROW
    BEGIN
        INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
        VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
    END
";

// Use multi_query for statements with semicolons
if ($mysqli->multi_query($triggerSql)) {
    echo "Trigger created successfully.";
} else {
    echo "Error creating trigger: " . $mysqli->error;
}

// Don't forget to clear results from multi_query
while ($mysqli->next_result()) {;}



// Assumes a connected 'connection' object from mysql2/promise
async function createTrigger(connection) {
    const triggerSql = `
        CREATE TRIGGER trg_customer_archive
        AFTER DELETE ON CUSTOMERS
        FOR EACH ROW
        BEGIN
            INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY)
            VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY);
        END
    `;
    try {
        // The driver handles multi-statement queries correctly
        await connection.query(triggerSql);
        console.log('Trigger created successfully.');
    } catch (err) {
        console.error('Failed to create trigger:', err);
    }
}



// Assumes a connected 'conn' object (java.sql.Connection)
String triggerSql = ""
    + "CREATE TRIGGER trg_customer_archive "
    + "AFTER DELETE ON CUSTOMERS "
    + "FOR EACH ROW "
    + "BEGIN "
    + "    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) "
    + "    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); "
    + "END";

try (Statement stmt = conn.createStatement()) {
    stmt.execute(triggerSql);
    System.out.println("Trigger created successfully.");
} catch (SQLException e) {
    e.printStackTrace();
}



# Assumes a 'connection' object and a 'cursor' from mysql-connector-python
# Creating triggers with multiple statements can be tricky. It's often easier
# to run them one by one or from a script file.

trigger_definition = (""
    "CREATE TRIGGER trg_customer_archive "
    "AFTER DELETE ON CUSTOMERS "
    "FOR EACH ROW "
    "BEGIN "
    "    INSERT INTO OLD_CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) "
    "    VALUES(OLD.ID, OLD.NAME, OLD.AGE, OLD.ADDRESS, OLD.SALARY); "
    "END")

try:
    # The mysql connector can handle multi-statement blocks if enabled
    # but executing a simple CREATE TRIGGER statement is direct.
    cursor.execute(trigger_definition)
    print("Trigger created successfully.")
except mysql.connector.Error as err:
    # Check if trigger already exists
    if err.errno == 1359:
         print("Trigger already exists.")
    else:
        print(f"Error: {err}")