BEFORE and AFTER Triggers in SQL – Explained with Syntax & Examples

What Are BEFORE and AFTER Triggers?

Triggers in SQL automatically execute custom logic before or after specific data manipulation events (INSERT, UPDATE, or DELETE) on a table.

There are two key types:

• BEFORE Trigger: Executes before the database action takes place
• AFTER Trigger: Executes after the database action completes successfully

Understanding the difference is crucial for enforcing business rules, validating data, and auditing changes.

Difference Between BEFORE and AFTER Triggers

Syntax

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

Example 1: BEFORE INSERT Trigger

Prevent inserting users with empty emails:

CREATE TRIGGER before_insert_user
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
   IF NEW.email IS NULL OR NEW.email = '' THEN
     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email cannot be empty';
   END IF;
END;

Executed before the insert. If the email is blank, the insertion fails.

Example 2: AFTER INSERT Trigger

Log every new customer record into an audit table:

CREATE TRIGGER after_insert_customer
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
   INSERT INTO customer_log(customer_id, action, created_at)
   VALUES (NEW.id, 'INSERT', NOW());
END;

Executed after the new customer is successfully added.

Example 3: BEFORE UPDATE Trigger

Enforce salary constraint before update:

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 0 THEN
     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
   END IF;
END;

Example 4: AFTER DELETE Trigger

Record deletions to an archive table:

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO deleted_employees(id, name, deleted_at)
   VALUES (OLD.id, OLD.name, NOW());
END;

When to Use BEFORE vs AFTER