Mastering SQL Triggers with Practical Examples

What is an SQL Trigger?

An SQL trigger is a set of instructions that automatically execute (or ‘fire’) when a specified event occurs in a database table. Events can include actions like INSERT, UPDATE, or DELETE. Triggers are used for various purposes, such as enforcing business rules, auditing changes, or maintaining data integrity.

Example 1: Creating a Simple Trigger

Let’s start with a simple example. We want to log any changes made to a users table into a user_changes log table. Here’s how to create a trigger for this:

Step 1: Create the Log Table

CREATE TABLE user_changes (
    change_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    change_type VARCHAR(10),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the Trigger

CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_changes (user_id, change_type)
    VALUES (NEW.id, 'UPDATE');
END;

Explanation:

• BEFORE UPDATE ON users: The trigger will execute before any update on the users table.
• FOR EACH ROW: This specifies that the trigger will execute for each row affected by the update.
• NEW.id: This references the id of the row that is being updated.

Example 2: Using a Trigger to Prevent Deletion

Suppose we want to prevent users from being deleted if they have outstanding orders. We can create a trigger that raises an error when trying to delete a user with orders:

Step 1: Create the Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE
);

Step 2: Create the Trigger

CREATE TRIGGER prevent_user_deletion
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    DECLARE order_count INT;
    SELECT COUNT(*) INTO order_count FROM orders WHERE user_id = OLD.id;
    IF order_count > 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot delete user with outstanding orders';
    END IF;
END;

Explanation:

• OLD.id: This refers to the id of the user that is being deleted.
• SIGNAL SQLSTATE: This raises an error if the condition is met, preventing the deletion.

Example 3: Maintaining an Audit Trail

In some cases, you may want to keep a detailed audit trail of changes in your tables. Here’s how you can do that using triggers:

Step 1: Create an Audit Table

CREATE TABLE audit_log (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50),
    operation VARCHAR(10),
    changed_data JSON,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the Trigger for Inserts

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, operation, changed_data)
    VALUES ('users', 'INSERT', JSON_OBJECT('id', NEW.id, 'name', NEW.name));
END;

Explanation:

• AFTER INSERT ON users: This trigger fires after a new user is inserted into the users table.
• JSON_OBJECT(): This function formats the changed data as JSON for easier tracking.

Conclusion

SQL triggers are a powerful feature that can help automate tasks and enforce rules within your database. By using the examples provided, you can start implementing triggers in your own SQL projects to improve data integrity and auditing capabilities.