Triggers in SQL: Automating Database Behavior

Table of Contents

1. Introduction
2. What Is a Trigger in SQL?
3. Why Use Triggers?
4. Types of Triggers
5. Syntax of a Trigger
6. BEFORE vs AFTER Triggers
7. INSERT, UPDATE, and DELETE Triggers
8. Trigger Events: Row-Level vs Statement-Level
9. Creating a Simple Trigger
10. Using NEW and OLD Pseudorecords
11. Real-World Example: Logging Table Changes
12. Real-World Example: Enforcing Business Logic
13. Preventing Invalid Updates
14. Using Triggers for Auditing
15. Nested and Recursive Triggers
16. Managing Trigger Order and Dependencies
17. Performance Considerations
18. Best Practices for Triggers
19. Viewing, Modifying, and Dropping Triggers
20. Summary and What’s Next

1. Introduction

SQL triggers allow developers to automate actions in response to events on a table. Whether you’re auditing changes, enforcing complex rules, or cascading updates, triggers help enforce consistent behavior directly in the database.

2. What Is a Trigger in SQL?

A trigger is a stored set of instructions that is automatically executed (or “triggered”) when a specific database event occurs, such as an INSERT, UPDATE, or DELETE.

3. Why Use Triggers?

• Automatically enforce business rules
• Log changes for auditing
• Validate or transform data
• Synchronize or replicate changes
• Prevent invalid operations
• Reduce redundant application code

4. Types of Triggers

5. Syntax of a Trigger (General Form)

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

Varies slightly depending on DBMS.

6. BEFORE vs AFTER Triggers

• BEFORE: Ideal for validations or modifications to data before committing
• AFTER: Ideal for logging or notifications after the change is committed

7. INSERT, UPDATE, and DELETE Triggers

8. Trigger Events: Row-Level vs Statement-Level

• Row-Level Triggers: Executes once per row affected
• Statement-Level Triggers: Executes once per statement regardless of row count

PostgreSQL supports both via FOR EACH ROW and FOR EACH STATEMENT.

9. Creating a Simple Trigger

Example: Add timestamp on new row (MySQL):

sqlCopyEditCREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

This automatically sets created_at on insert.

10. Using NEW and OLD Pseudorecords

• NEW.column_name refers to the new value in INSERT/UPDATE
• OLD.column_name refers to the previous value in UPDATE/DELETE

sqlCopyEditCREATE TRIGGER audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

11. Real-World Example: Logging Table Changes

sqlCopyEditCREATE TRIGGER log_user_activity
AFTER UPDATE ON users
FOR EACH ROW
INSERT INTO user_logs (user_id, action, timestamp)
VALUES (OLD.id, 'Profile Updated', NOW());

12. Real-World Example: Enforcing Business Logic

sqlCopyEditCREATE TRIGGER prevent_negative_stock
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.stock < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Stock cannot be negative';
  END IF;
END;

13. Preventing Invalid Updates

sqlCopyEditCREATE TRIGGER block_salary_cut
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Salary decrease not allowed';
  END IF;
END;

14. Using Triggers for Auditing

sqlCopyEditCREATE TRIGGER audit_delete_orders
AFTER DELETE ON orders
FOR EACH ROW
INSERT INTO deleted_orders (order_id, deleted_at)
VALUES (OLD.id, NOW());

Useful for tracking deletions or soft-deletion implementation.

15. Nested and Recursive Triggers

• Nested triggers: Trigger fired by another trigger
• Recursive triggers: A trigger firing itself directly or indirectly
  • May be disabled by default or require special config

Use SET NOCOUNT ON or control flags to avoid infinite loops.

16. Managing Trigger Order and Dependencies

Some databases (e.g., Oracle, SQL Server) allow you to define trigger execution order.

In general:

• Use modular triggers per use case
• Avoid creating large monolithic triggers

17. Performance Considerations

• Triggers add overhead — especially on bulk operations
• Use only where essential
• Avoid complex logic that introduces latency
• Beware of hidden side-effects (e.g., unexpected updates)

18. Best Practices for Triggers

• Name triggers clearly: e.g., before_insert_users, audit_delete_customers
• Keep them short and specific
• Use AFTER triggers for non-critical tasks
• Always test thoroughly before deploying
• Document trigger purpose and logic for future maintainers

19. Viewing, Modifying, and Dropping Triggers

View existing triggers:

sqlCopyEditSHOW TRIGGERS;              -- MySQL  
SELECT * FROM pg_trigger;   -- PostgreSQL  

Drop a trigger:

sqlCopyEditDROP TRIGGER trigger_name ON table_name; -- MySQL  
DROP TRIGGER trigger_name;               -- PostgreSQL  

20. Summary and What’s Next

Triggers allow automatic execution of logic in response to table events, making them ideal for auditing, validation, logging, and enforcing business rules. Use them wisely to ensure consistency without adding hidden complexity.