Advanced SQL Triggers: Internals, Auditing & Performance

In This Chapter

Internal Workings of Triggers
Triggers for Auditing & Business Rules
Performance Considerations

1 Internal Workings of Triggers

Beyond the intermediate coverage, let's understand how triggers interact with the query execution pipeline.

Trigger Execution Context

A trigger executes within the same transaction as the statement that fired it. If the trigger fails, the entire statement rolls back. This means:

Trigger errors cause the original INSERT/UPDATE/DELETE to fail
Triggers see uncommitted data from the current transaction
Triggers hold the same locks as the parent statement
A ROLLBACK in the calling procedure also rolls back trigger actions

Multiple Triggers on the Same Event

MySQL 5.7+

-- You can have multiple triggers on the same event
CREATE TRIGGER trg_audit_first
BEFORE UPDATE ON orders
FOR EACH ROW
FOLLOWS trg_validate  -- Execute AFTER trg_validate
BEGIN
    -- Audit logic
END;

-- FOLLOWS / PRECEDES controls execution order

Key Takeaways

Triggers run within the same transaction as the firing statement
A trigger failure rolls back the entire statement
Use FOLLOWS/PRECEDES to control order when multiple triggers exist

2 Triggers for Auditing & Business Rules

Top

Complete Audit Trail

SQL

CREATE TABLE audit_trail (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    table_name  VARCHAR(50),
    action      VARCHAR(10),
    row_id      INT,
    old_data    JSON,
    new_data    JSON,
    changed_by  VARCHAR(100),
    changed_at  DATETIME DEFAULT NOW()
);

DELIMITER //
CREATE TRIGGER trg_employees_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail (table_name, action, row_id, old_data, new_data, changed_by)
    VALUES (
        'employees', 'UPDATE', NEW.id,
        JSON_OBJECT('name', OLD.name, 'salary', OLD.salary, 'dept', OLD.department),
        JSON_OBJECT('name', NEW.name, 'salary', NEW.salary, 'dept', NEW.department),
        CURRENT_USER()
    );
END //
DELIMITER ;

Business Rule Enforcement

SQL

DELIMITER //
CREATE TRIGGER trg_prevent_salary_decrease
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END //
DELIMITER ;

JSON audit logs are incredibly useful for debugging. You can query SELECT * FROM audit_trail WHERE JSON_EXTRACT(old_data, '$.salary') != JSON_EXTRACT(new_data, '$.salary') to find all salary changes.

Key Takeaways

Store OLD and NEW values as JSON for flexible audit trails
Use BEFORE triggers with SIGNAL to enforce business rules
Record CURRENT_USER() and timestamps for accountability

3 Performance Considerations

Top

Trigger Performance Impact

Factor	Impact	Mitigation
Number of triggers per table	Each fires on every DML operation	Combine logic into fewer triggers
Queries inside triggers	Extra I/O per row processed	Minimize queries; avoid JOINs in triggers
Trigger chains (cascading)	Exponential overhead	Avoid trigger A updating table B which triggers C
Lock duration	Triggers extend how long rows are locked	Keep trigger logic fast and simple

When NOT to use triggers: (1) Complex business logic — use stored procedures or application code instead. (2) Heavy computation — triggers run for every affected row; a bulk UPDATE on 100K rows runs the trigger 100K times. (3) Cross-database operations — triggers can't easily reach other databases. Consider application-level event systems for complex workflows.

Key Takeaways

Triggers add overhead to every DML statement — keep them lightweight
Avoid trigger chains and recursive cascading updates
For complex logic, prefer procedures or application code over triggers
Monitor trigger performance with SHOW PROFILE

What's next? Managing Transactions & Concurrency — ACID deep-dive, isolation levels, and deadlock handling.

All Chapters in SQL Advanced Course

Previous ChapterAdvanced Procedures Next Chapter Managing Transactions

Advanced Triggers

In This Chapter

1 Internal Workings of Triggers

Trigger Execution Context

Multiple Triggers on the Same Event

Key Takeaways

2 Triggers for Auditing & Business Rules

Complete Audit Trail

Business Rule Enforcement

Key Takeaways

3 Performance Considerations

Trigger Performance Impact

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect