SQL Triggers Explained with Examples

Published March 11, 2025 | By matt

1. What is an SQL Trigger?

An SQL trigger is a special type of stored procedure that automatically executes in response to a specified event on a database table, such as an INSERT, UPDATE, or DELETE operation.

Key points:

  • Triggers run automatically when the specified event occurs.
  • They are used for enforcing business rules, data integrity, auditing, and automating database actions.
  • Unlike stored procedures, triggers do not require manual execution.

2. Types of SQL Triggers

SQL triggers can be categorized based on when they execute:

  • BEFORE Trigger: Executes before an INSERT, UPDATE, or DELETE operation.
  • AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE operation.
  • INSTEAD OF Trigger: Used in place of an INSERT, UPDATE, or DELETE operation, often on views.

3. Syntax of an SQL Trigger

Here’s the general syntax for creating a trigger:

sql
CREATE TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF INSERT | UPDATE | DELETE
ON table_name
FOR EACH ROW
BEGIN
-- SQL statements to execute
END;

4. SQL Trigger Examples

Example 1: Auditing Changes with an AFTER UPDATE Trigger

Let’s say we have an employees table, and we want to track salary changes in an audit_log table.

sql
CREATE TABLE audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO audit_log (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);
END IF;
END;

Explanation:

  • This trigger fires after an update on the employees table.
  • If the salary changes, it logs the old and new salary in audit_log.

Example 2: Enforcing Business Rules with a BEFORE INSERT Trigger

Imagine we want to prevent inserting employees with a salary below $30,000.

sql
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary < 30000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Salary must be at least $30,000';
END IF;
END;

Explanation:

  • This trigger prevents inserting a row if the salary is below $30,000 by raising an error.

Example 3: Automatically Deleting Related Records with an AFTER DELETE Trigger

If an employee is deleted, we also want to remove their records from the timesheets table.

sql
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
DELETE FROM timesheets WHERE employee_id = OLD.id;
END;

Explanation:

  • This trigger ensures that when an employee is deleted, related timesheets entries are also removed.

5. Best Practices for Using SQL Triggers

  • Avoid complex logic in triggers to maintain performance.
  • Use AFTER triggers for logging changes and auditing.
  • Use BEFORE triggers to validate data before insertion.
  • Be careful with INSTEAD OF triggers, as they replace normal operations.
  • Test triggers thoroughly before deploying them in production.

Final Thoughts

SQL triggers are powerful for automating tasks and enforcing data rules, but they should be used wisely to avoid performance issues.

Recent Posts

Recent Comments

No comments to show.


Place Important Links Here

Copyright © 2020 - All Rights Reserved.

Disclaimers And Legal Stuff Here

Powered by WordPress and Simple Affiliate WordPress Theme