MySQL - Before Delete Trigger

MySQL BEFORE DELETE Triggers: A Modern Guide

In modern database management, triggers are powerful tools for automating actions. A trigger is a named database object that is associated with a table, and it activates when a particular event (like an INSERT, UPDATE, or DELETE) occurs for the table. This tutorial focuses on the BEFORE DELETE trigger, a crucial component for tasks like auditing, archiving, and enforcing complex business rules.

Understanding Triggers: Core Concepts

Triggers are essentially event-driven stored procedures. They run automatically, meaning you don’t call them directly from your application. They are categorized by timing (BEFORE or AFTER the event) and the event itself (INSERT, UPDATE, DELETE).

• BEFORE Triggers: Execute before the data modification (e.g., DELETE) occurs. They are ideal for validating data or preparing related data before the primary action is committed.
• AFTER Triggers: Execute after the data modification is completed. They are typically used for logging actions or updating other tables based on the change.

The MySQL BEFORE DELETE Trigger

A BEFORE DELETE trigger fires for each row that is about to be deleted from a table. Within this trigger, you have access to the data of the row being deleted using the OLD alias. For instance, OLD.column_name refers to the value of column_name in the row before it is removed. This is incredibly useful for capturing a final snapshot of the data.

Practical Use Case: Archiving. When a user is deleted from the main users table, a BEFORE DELETE trigger can automatically copy their information into an archived_users table, ensuring no data is permanently lost. This is a common pattern for maintaining historical records and compliance.

Syntax

The fundamental syntax for creating a BEFORE DELETE trigger is as follows:

CREATE TRIGGER trigger_name
    BEFORE DELETE ON table_name
    FOR EACH ROW
BEGIN
    -- Your SQL statements go here.
    -- You can access the row to be deleted using the OLD alias.
    -- Example: INSERT INTO archive_table (id, data) VALUES (OLD.id, OLD.data);
END;

Note: The DELIMITER command is often used in SQL clients to change the statement delimiter from ; to something else (like //), allowing the trigger body, which may contain semicolons, to be parsed as a single statement.

Comprehensive Example: Archiving Deleted Customers

Let’s build a practical example. We’ll have a customers table for active customers and an archived_customers table to store data of deleted customers.

Step 1: Create the tables. Notice the archived_at column in the archive table to timestamp the deletion.

-- Main table for active customers
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    signup_date DATE NOT NULL
);

-- Table to archive deleted customers
CREATE TABLE archived_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    signup_date DATE NOT NULL,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Populate the customers table with some sample data.

INSERT INTO customers (name, email, signup_date) VALUES
('Alice Johnson', 'alice@example.com', '2023-01-15'),
('Bob Williams', 'bob@example.com', '2023-02-20'),
('Charlie Brown', 'charlie@example.com', '2023-03-10');

Step 3: Create the BEFORE DELETE trigger to handle the archival process.

DELIMITER //

CREATE TRIGGER archive_customer_before_delete
    BEFORE DELETE ON customers
    FOR EACH ROW
BEGIN
    -- Insert the record being deleted into the archive table.
    INSERT INTO archived_customers (id, name, email, signup_date)
    VALUES (OLD.id, OLD.name, OLD.email, OLD.signup_date);
END//

DELIMITER ;

Step 4: Execute a DELETE statement to test the trigger.

DELETE FROM customers WHERE email = 'bob@example.com';

Verification

Now, let’s verify that the trigger worked as expected by querying both tables.

Querying the customers table shows Bob has been removed:

SELECT * FROM customers;

id	name	email	signup_date
1	Alice Johnson	alice@example.com	2023-01-15
3	Charlie Brown	charlie@example.com	2023-03-10

Querying the archived_customers table shows Bob’s data has been successfully archived:

SELECT id, name, email, signup_date FROM archived_customers;

id	name	email	signup_date
2	Bob Williams	bob@example.com	2023-02-20

Managing Triggers from Application Code

While triggers are defined in the database, your application code is what initiates the events that fire them. The trigger’s execution is transparent to the application; you simply run a DELETE statement, and the database handles the rest. Below are modern, robust examples of how to execute a DELETE statement in various languages, assuming the trigger we defined above already exists in the database.

Best Practices for Client Code

• Use Prepared Statements: Always use parameterized queries to prevent SQL injection vulnerabilities.
• Connection Management: Ensure database connections are opened securely and closed reliably, often using try-catch-finally or language-specific constructs like try-with-resources.
• Configuration Management: Store database credentials in environment variables or a secure configuration file, not in your source code.

Python
Node.js
Java
PHP



This Python example uses the `mysql-connector-python` library and demonstrates best practices like using environment variables for credentials and proper error handling.

```python
import mysql.connector
import os

def delete_customer_by_email(email):
    """Deletes a customer and relies on the trigger to archive them."""
    connection = None
    try:
        # Best practice: Use environment variables for configuration
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=os.getenv('DB_NAME', 'TUTORIALS')
        )
        cursor = connection.cursor()

        # Use prepared statements to prevent SQL injection
        delete_query = "DELETE FROM customers WHERE email = %s"

        cursor.execute(delete_query, (email,))
        connection.commit() # Important: commit the transaction

        if cursor.rowcount > 0:
            print(f"Successfully deleted customer with email: {email}")
            print("The BEFORE DELETE trigger should have archived the record.")
        else:
            print(f"No customer found with email: {email}")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed.")

# --- To run this example ---
# 1. Make sure the 'customers' table and trigger exist.
# 2. Run the function:
delete_customer_by_email('charlie@example.com')

This Node.js example uses the modern mysql2/promise library with async/await for cleaner, more readable asynchronous code.

const mysql = require('mysql2/promise');

// Best practice: Use environment variables for configuration
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'password',
    database: process.env.DB_NAME || 'TUTORIALS'
};

async function deleteCustomerByEmail(email) {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);

        // Use prepared statements to prevent SQL injection
        const deleteQuery = 'DELETE FROM customers WHERE email = ?';

        const [result] = await connection.execute(deleteQuery, [email]);

        if (result.affectedRows > 0) {
            console.log(`Successfully deleted customer with email: ${email}`);
            console.log('The BEFORE DELETE trigger should have archived the record.');
        } else {
            console.log(`No customer found with email: ${email}`);
        }

    } catch (error) {
        console.error(`Database error: ${error.message}`);
    } finally {
        if (connection) {
            await connection.end();
            console.log('MySQL connection closed.');
        }
    }
}

// --- To run this example ---
// 1. Run `npm install mysql2`
// 2. Make sure the 'customers' table and trigger exist.
// 3. Run the function:
deleteCustomerByEmail('alice@example.com');

This Java example uses modern JDBC practices, including try-with-resources for automatic connection management and PreparedStatement for security.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TriggerExample {

    // Best practice: Use a properties file or env vars for credentials
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void deleteCustomerByEmail(String email) {
        String deleteSQL = "DELETE FROM customers WHERE email = ?";

        // try-with-resources ensures the connection and statement are closed
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(deleteSQL)) {

            pstmt.setString(1, email);
            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                System.out.println("Successfully deleted customer with email: " + email);
                System.out.println("The BEFORE DELETE trigger should have archived the record.");
            } else {
                System.out.println("No customer found with email: " + email);
            }

        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        // Assumes the 'customers' table and trigger exist.
        // Note: You need the MySQL JDBC driver in your classpath.
        deleteCustomerByEmail("alice@example.com");
    }
}

This PHP example uses the mysqli extension with an object-oriented style, prepared statements, and proper error handling.

<?php
// Best practice: Use environment variables or a secure config file
$dbhost = getenv('DB_HOST') ?: 'localhost';
$dbuser = getenv('DB_USER') ?: 'root';
$dbpass = getenv('DB_PASSWORD') ?: 'password';
$dbname = getenv('DB_NAME') ?: 'TUTORIALS';

// Enable mysqli to throw exceptions for errors
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

    $emailToDelete = 'alice@example.com';

    // Use prepared statements to prevent SQL injection
    $stmt = $mysqli->prepare("DELETE FROM customers WHERE email = ?");
    $stmt->bind_param("s", $emailToDelete);
    $stmt->execute();

    if ($stmt->affected_rows > 0) {
        echo "Successfully deleted customer with email: $emailToDelete\n";
        echo "The BEFORE DELETE trigger should have archived the record.\n";
    } else {
        echo "No customer found with email: $emailToDelete\n";
    }

    $stmt->close();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Database Error: " . $e->getMessage() . "\n";
    exit();
}

?>