SQL Triggers

Triggers in SQL are special types of stored procedures that automatically execute or "fire" in response to specific events on a table or view. They are useful for enforcing business rules, maintaining data integrity, and automating system tasks.

Types of Triggers

• DML Triggers (Data Manipulation Language)
  • BEFORE Triggers: Execute before an INSERT, UPDATE, or DELETE operation.
  • AFTER Triggers: Execute after an INSERT, UPDATE, or DELETE operation.

MySQL does not support DDL (Data Definition Language) or logon triggers.

Components of a Trigger

• Trigger Name: A unique name to identify the trigger.
• Triggering Event: The event that causes the trigger to fire (INSERT, UPDATE, DELETE).
• Triggering Table: The table to which the trigger is attached.
• Triggering Timing: Specifies whether the trigger should fire BEFORE or AFTER the triggering event.
• Trigger Body: The SQL statements that define what the trigger does when it fires.

Context Tables

• NEW: A pseudo-table that contains the new rows for INSERT and UPDATE operations.
• OLD: A pseudo-table that contains the old rows for DELETE and UPDATE operations.

Creating Triggers

Syntax

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
    -- trigger body
END;
    

Examples

1. AFTER INSERT Trigger

This trigger logs details into an audit table whenever a new record is inserted into the Employees table.

CREATE TABLE Employees (
    EmpID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(100),
    Salary DECIMAL(10, 2)
);

CREATE TABLE EmployeeAudit (
    AuditID INT AUTO_INCREMENT PRIMARY KEY,
    EmpID INT,
    AuditAction VARCHAR(50),
    AuditTime DATETIME
);

DELIMITER //

CREATE TRIGGER trgAfterInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAudit (EmpID, AuditAction, AuditTime)
    VALUES (NEW.EmpID, 'INSERT', NOW());
END;
//

DELIMITER ;
    

2. BEFORE UPDATE Trigger

This trigger ensures that salary increases do not exceed 10%.

DELIMITER //

CREATE TRIGGER trgBeforeUpdate
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF NEW.Salary > OLD.Salary * 1.10 THEN
        SET NEW.Salary = OLD.Salary * 1.10;
    END IF;
END;
//

DELIMITER ;
    

3. AFTER DELETE Trigger

This trigger logs deletion details into an audit table whenever a record is deleted from the Employees table.

DELIMITER //

CREATE TRIGGER trgAfterDelete
AFTER DELETE ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO EmployeeAudit (EmpID, AuditAction, AuditTime)
    VALUES (OLD.EmpID, 'DELETE', NOW());
END;
//

DELIMITER ;
    

Dropping Triggers

DROP TRIGGER is used to remove a trigger.

DROP TRIGGER IF EXISTS trgAfterInsert;
    

Considerations

• Performance: Triggers can impact performance since they add additional processing for each triggering event. Use them judiciously.
• Debugging: Debugging triggers can be challenging because they run automatically and can produce side effects that are not immediately visible.
• Transaction Handling: Triggers execute as part of the transaction that caused them to fire. If a trigger fails, it can roll back the entire transaction.
• Recursion and Nesting: Avoid designing triggers that cause recursive or nested triggers, which can lead to infinite loops.

Use Cases

• Auditing Changes: Tracking changes to sensitive data.
• Enforcing Business Rules: Ensuring business rules are adhered to at the database level.
• Maintaining Referential Integrity: Automatically updating or deleting related records.
• Complex Validation: Performing complex validation that is not possible with constraints.

Summary

Triggers are a powerful feature in SQL that allow you to automate actions in response to data changes. By understanding how to create, manage, and use triggers effectively, you can ensure data integrity, enforce business rules, and automate tasks within your database.