SQL Triggers

Triggers are special stored procedures that automatically execute when specific database events occur. They enforce business rules, maintain data integrity, and audit changes without explicit calls.

Trigger Architecture

flowchart TB
    A[DML/DDL Operation] --> B{Trigger Event}
    B -->|INSERT| C[Trigger Fires]
    B -->|UPDATE| C
    B -->|DELETE| C
    
    C --> D[Execute Trigger Logic]
    D --> E[Business Rules]
    D --> F[Data Validation]
    D --> G[Audit Logging]
    
    E --> H[Complete Operation]
    F --> H
    G --> H
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0

Key Points:

• Automatic Execution: Fires automatically on events, no explicit call needed
• Event-Driven: Responds to INSERT, UPDATE, DELETE, or DDL operations
• Transparent: Applications don't need to know triggers exist
• Data Integrity: Enforces business rules and maintains consistency
• Audit Trail: Automatically logs changes for compliance and tracking

Trigger Types Hierarchy

graph TB
    A[SQL Triggers] --> B[DML Triggers]
    A --> C[DDL Triggers]
    A --> D[LOGON Triggers]
    
    B --> E[BEFORE]
    B --> F[AFTER]
    B --> G[INSTEAD OF]
    
    E --> E1[Modify before save]
    E --> E2[Validate data]
    
    F --> F1[Audit changes]
    F --> F2[Update related tables]
    
    G --> G1[Replace operation]
    G --> G2[Handle views]
    
    C --> C1[Schema changes]
    D --> D1[Login events]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0

Key Points:

• DML Triggers: Fire on data manipulation (INSERT, UPDATE, DELETE)
• BEFORE: Execute before operation, can modify data
• AFTER: Execute after operation completes, for auditing
• INSTEAD OF: Replace the operation entirely, useful for views
• DDL Triggers: Fire on schema changes (CREATE, ALTER, DROP)

BEFORE Trigger Execution Flow

sequenceDiagram
    participant App as Application
    participant Trigger as BEFORE Trigger
    participant Validation as Validation Logic
    participant Table as Database Table
    
    App->>Trigger: INSERT INTO employees
    Trigger->>Validation: Check NEW values
    Validation->>Validation: Validate salary > 0
    Validation->>Trigger: Modify NEW.email = LOWER
    Trigger->>Trigger: Set NEW.created_at = NOW
    Trigger->>Table: Insert modified data
    Table->>App: Success
    
    Note over Trigger: Can modify data<br/>before insertion

Key Points:

• Pre-Processing: Executes before data is written to table
• Data Modification: Can change NEW values before insertion
• Validation: Prevent invalid data by raising errors
• Default Values: Set computed or default values automatically
• MySQL/PostgreSQL: Supported, not available in SQL Server

AFTER Trigger Execution Flow

sequenceDiagram
    participant App as Application
    participant Table as Database Table
    participant Trigger as AFTER Trigger
    participant Audit as Audit Log
    participant Related as Related Tables
    
    App->>Table: UPDATE employees
    Table->>Table: Commit changes
    Table->>Trigger: Fire AFTER trigger
    Trigger->>Audit: Log change
    Trigger->>Related: Update aggregates
    Trigger->>App: Complete
    
    Note over Trigger: Cannot modify<br/>triggering data

Key Points:

• Post-Processing: Executes after operation completes successfully
• Read-Only: Cannot modify the data that triggered it
• Audit Trail: Perfect for logging changes to audit tables
• Cascading Updates: Update related tables or aggregates
• All Databases: Supported by all major database systems

INSTEAD OF Trigger Flow

flowchart TB
    A[INSERT INTO view] --> B[INSTEAD OF Trigger]
    B --> C[Cancel Original Operation]
    C --> D[Custom Logic]
    
    D --> E[Validate Data]
    D --> F[Transform Data]
    D --> G[Insert into Base Tables]
    
    E --> H[Complete]
    F --> H
    G --> H
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style H fill:#00BCD4

Key Points:

• Operation Replacement: Cancels original DML, executes custom logic
• View Updates: Makes non-updatable views updatable
• Complex Logic: Handle multi-table inserts/updates
• SQL Server/Oracle/PostgreSQL: Supported platforms

Trigger Execution Order

flowchart LR
    A[DML Statement] --> B[BEFORE Trigger]
    B --> C[Constraint Check]
    C --> D[DML Execution]
    D --> E[AFTER Trigger]
    E --> F[Commit]
    
    G[Error at any step] --> H[Rollback All]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style D fill:#FF9800
    style E fill:#9C27B0
    style F fill:#00BCD4
    style H fill:#F44336

Key Points:

• Sequential Execution: BEFORE → Constraints → DML → AFTER
• Transaction Scope: All steps in single transaction
• Rollback: Error at any step rolls back entire operation
• Multiple Triggers: Execute in creation order (or specified order)

INSERTED and DELETED Tables (SQL Server)

graph TB
    A[Trigger Execution] --> B[INSERTED Table]
    A --> C[DELETED Table]
    
    B --> B1[INSERT: New rows]
    B --> B2[UPDATE: New values]
    
    C --> C1[DELETE: Removed rows]
    C --> C2[UPDATE: Old values]
    
    D[Access Pattern] --> E[SELECT from INSERTED]
    D --> F[SELECT from DELETED]
    D --> G[Compare OLD vs NEW]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0

Key Points:

• INSERTED: Contains new rows (INSERT) or new values (UPDATE)
• DELETED: Contains removed rows (DELETE) or old values (UPDATE)
• Temporary Tables: Exist only during trigger execution
• SQL Server Specific: Other databases use NEW/OLD keywords

Trigger Use Cases

graph LR
    A[Common Use Cases] --> B[Audit Trail]
    A --> C[Data Validation]
    A --> D[Referential Integrity]
    A --> E[Derived Data]
    
    B --> B1[Log all changes]
    C --> C1[Business rules]
    D --> D1[Cascade operations]
    E --> E1[Update aggregates]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336

Key Points:

• Audit Trail: Automatically log who changed what and when
• Validation: Enforce complex business rules beyond constraints
• Referential Integrity: Maintain relationships between tables
• Derived Data: Keep calculated fields and aggregates current

Code Examples

BEFORE Trigger (MySQL)

-- Validate and transform data before insert
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Validate salary
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
    
    -- Transform email to lowercase
    SET NEW.email = LOWER(NEW.email);
    
    -- Set timestamps
    SET NEW.created_at = NOW();
END;

AFTER Trigger (SQL Server)

-- Audit trail for employee changes
CREATE TRIGGER after_employee_update
ON employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO employee_audit (
        employee_id, 
        old_salary, 
        new_salary, 
        changed_by, 
        changed_at
    )
    SELECT 
        i.employee_id,
        d.salary,
        i.salary,
        SYSTEM_USER,
        GETDATE()
    FROM inserted i
    INNER JOIN deleted d ON i.employee_id = d.employee_id
    WHERE i.salary <> d.salary;
END;

INSTEAD OF Trigger (PostgreSQL)

-- Make view updatable
CREATE OR REPLACE FUNCTION instead_of_view_insert()
RETURNS TRIGGER AS $$
BEGIN
    -- Insert into base table
    INSERT INTO employees (first_name, last_name, email)
    VALUES (NEW.first_name, NEW.last_name, NEW.email);
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER view_insert_trigger
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
EXECUTE FUNCTION instead_of_view_insert();

DDL Trigger (SQL Server)

-- Prevent table drops
CREATE TRIGGER prevent_table_drop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Table drops are not allowed!';
    ROLLBACK;
END;

Best Practices

1. Keep Logic Simple: Avoid complex operations that slow down DML
2. Avoid Recursion: Prevent triggers from calling themselves
3. Error Handling: Use proper exception handling and rollback
4. Document: Comment trigger purpose and dependencies
5. Test Thoroughly: Test with various scenarios and edge cases
6. Performance: Index columns used in trigger conditions

---