SQL Triggers

A trigger is a specialized, event-driven stored program that automatically executes (or "fires") in response to a specific database event on a target table or view. Unlike standard stored procedures, triggers cannot be invoked manually using EXEC or CALL; they are executed implicitly by the database management system whenever the triggering action occurs.

Triggers are primarily used to enforce complex business rules, maintain automated audit logs, synchronize replicated data, and prevent invalid state changes that simple declarative constraints (CHECK, FOREIGN KEY) cannot easily express.

Trigger Events & Timing

Triggers are defined by two primary characteristics: when they execute relative to the event and what database operation fires them.

1. Triggering Events

• INSERT: Fires when new records are added.
• UPDATE: Fires when existing records are modified.
• DELETE: Fires when records are removed.

2. Execution Timing

• **BEFORE / FOR / AFTER**: Specifies whether the trigger runs before or after the triggering DML statement completes.
• INSTEAD OF: Intercepts the triggering operation completely and executes custom SQL logic in place of the original command (frequently used to make complex multi-table views updatable).

                      DML OPERATION ATTEMPT
                   (INSERT, UPDATE, or DELETE)
                               |
                               v
                     +-------------------+
                     | BEFORE Trigger    |  ---> Cancels operation if validation fails
                     +-------------------+
                               |
                               v
                   [ Base Table Modification ]
                               |
                               v
                     +-------------------+
                     | AFTER Trigger     |  ---> Handles logging, alerts, cascades
                     +-------------------+

Pseudo-Tables: Accessing Data States

When a trigger fires, the database engine makes special virtual transition tables (or memory references) available inside the trigger scope. These allow you to inspect both the previous state and the new state of affected records:

Transition Data Availability Matrix:

• INSERT: Contains NEW / inserted data only (OLD is null/empty).
• DELETE: Contains OLD / deleted data only (NEW is null/empty).
• UPDATE: Contains both OLD (original values) and NEW (proposed values).

Implementation Examples Across Engines

1. SQL Server: Audit Log Trigger (AFTER UPDATE)

This trigger logs changes made to employee salary fields in an external SalaryAuditLog table whenever an UPDATE query affects the Employees table:

CREATE TRIGGER trg_AuditEmployeeSalary
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Only log if the Salary column was modified
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryAuditLog (
            EmployeeID, 
            OldSalary, 
            NewSalary, 
            ModifiedDate, 
            ModifiedBy
        )
        SELECT 
            d.EmployeeID,
            d.Salary AS OldSalary,
            i.Salary AS NewSalary,
            GETDATE(),
            SYSTEM_USER
        FROM deleted d
        JOIN inserted i ON d.EmployeeID = i.EmployeeID
        WHERE d.Salary <> i.Salary;
    END
END;
GO

2. PostgreSQL: Row-Level Validation (BEFORE INSERT)

In PostgreSQL, a trigger function is declared separately using PL/pgSQL and then attached to the target table:

-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION fn_validate_order_quantity()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Prevent inserting negative or zero order quantities
    IF NEW.Quantity <= 0 THEN
        RAISE EXCEPTION 'Order quantity must be greater than zero. Provided: %', NEW.Quantity;
    END IF;
    
    RETURN NEW; -- Proceed with the insert
END;
$$;

-- Step 2: Attach the trigger to the table
CREATE TRIGGER trg_check_quantity
BEFORE INSERT OR UPDATE ON OrderItems
FOR EACH ROW
EXECUTE FUNCTION fn_validate_order_quantity();

3. MySQL: Enforcing Complex Defaults (BEFORE INSERT)

DELIMITER //

CREATE TRIGGER trg_set_sku_before_insert
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
    -- Automatically generate SKU if left blank
    IF NEW.SKU IS NULL OR NEW.SKU = '' THEN
        SET NEW.SKU = CONCAT('PROD-', UPPER(SUBSTRING(NEW.ProductName, 1, 3)), '-', UNIX_TIMESTAMP());
    END IF;
END //

DELIMITER ;

Row-Level vs. Statement-Level Triggers

• Row-Level Triggers (FOR EACH ROW): Executes once for every individual row modified by the query. If an UPDATE statement modifies 500 rows, a row-level trigger executes 500 times.
• Statement-Level Triggers (FOR EACH STATEMENT): Executes once per query, regardless of how many rows are impacted (0, 1, or 10,000). Useful for blanket auditing or batch validation.

Note: SQL Server triggers are always statement-level (the inserted and deleted tables hold all affected rows for the entire batch), whereas MySQL only supports row-level triggers. PostgreSQL supports both.

Best Practices & Warnings

1. Keep Triggers Lightweight: Because triggers execute synchronously inside the user's active transaction, expensive operations (like network calls, complex joins, or heavy math) will slow down all write operations.
2. Avoid Hidden Side-Effects: Triggers operate implicitly behind the scenes. Obscuring core business logic inside multi-layered triggers can make debugging, maintenance, and query troubleshooting difficult for other developers.
3. Prevent Infinite Recursion: If a trigger on TableA updates TableB, and a trigger on TableB updates TableA, the engine may crash due to infinite recursion unless recursive triggers are disabled or guarded.
4. Use Declarative Constraints First: Never use a trigger to do what a standard FOREIGN KEY, CHECK, DEFAULT, or UNIQUE constraint can accomplish natively at the engine level.

Managing Triggers

Disabling / Enabling a Trigger (SQL Server / Oracle)

-- Disable
ALTER TABLE Employees DISABLE TRIGGER trg_AuditEmployeeSalary;

-- Enable
ALTER TABLE Employees ENABLE TRIGGER trg_AuditEmployeeSalary;

Dropping a Trigger

-- SQL Server
DROP TRIGGER IF EXISTS trg_AuditEmployeeSalary;

-- PostgreSQL
DROP TRIGGER IF EXISTS trg_check_quantity ON OrderItems;

-- MySQL
DROP TRIGGER IF EXISTS trg_set_sku_before_insert;