AFTER trigger execution in SQL

Introduction

An AFTER trigger runs right after a change happens in the database. It helps you do extra work automatically once data is added, changed, or removed.

You want to log changes after someone updates a record.

You need to update related tables after inserting new data.

You want to send notifications after deleting a record.

You want to check or enforce rules after data changes.

You want to keep audit trails automatically after data updates.

Syntax

SQL

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END;

AFTER triggers run only after the main action (insert, update, delete) completes successfully.

You can create separate triggers for INSERT, UPDATE, or DELETE events.

Examples

This trigger adds a log entry after a new customer is inserted.

SQL

CREATE TRIGGER after_insert_customer
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(action, description) VALUES ('INSERT', 'New customer added');
END;

This trigger logs when an order is deleted.

SQL

CREATE TRIGGER after_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(action, description) VALUES ('DELETE', 'Order removed');
END;

Sample Program

This example creates two tables: employees and salary_changes. The AFTER UPDATE trigger records every salary change in salary_changes. When Alice's salary is updated, the trigger saves the old and new salary.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT
);

CREATE TABLE salary_changes (
  emp_id INT,
  old_salary INT,
  new_salary INT,
  change_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_changes(emp_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
END;

INSERT INTO employees VALUES (1, 'Alice', 5000);
UPDATE employees SET salary = 5500 WHERE id = 1;

SELECT * FROM salary_changes;

Important Notes

AFTER triggers cannot modify the table that fired them directly to avoid recursion.

Use AFTER triggers to ensure the main data change is successful before running extra actions.

Summary

AFTER triggers run after data changes like insert, update, or delete.

They help automate tasks like logging, notifications, or updating related data.

They only run if the main action succeeds, so they keep data consistent.