AFTER trigger execution in SQL - Step-by-Step Execution

Concept Flow - AFTER trigger execution

DML Operation (INSERT/UPDATE/DELETE)

↓

Row(s) affected in table

↓

AFTER Trigger fires

↓

Trigger code executes

↓

Trigger completes

↓

Original DML transaction completes

After a data change (insert, update, delete) happens, the AFTER trigger runs its code, then the original operation finishes.

Execution Sample

SQL

CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (NEW.id, 'Inserted');
END;

This trigger runs after a new employee is inserted, adding a record to the audit_log table.

Execution Table

Step	Action	Table State	Trigger Fired?	Trigger Action	Result
1	INSERT new employee (id=101)	employees: +1 row (id=101)	No	N/A	Employee added
2	AFTER INSERT trigger fires	employees: 1 new row	Yes	Insert audit_log (id=101, 'Inserted')	Audit log updated
3	Trigger completes	employees: 1 new row, audit_log: +1 row	Yes	Trigger ends	Trigger done
4	Original INSERT transaction completes	employees: 1 new row, audit_log: 1 new row	Yes	N/A	Transaction committed

💡 Trigger runs after the insert operation, then the transaction commits successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
employees rows	0	1 (id=101)	1 (id=101)	1 (id=101)	1 (id=101)
audit_log rows	0	0	1 (id=101, 'Inserted')	1 (id=101, 'Inserted')	1 (id=101, 'Inserted')
Trigger fired	No	No	Yes	Yes	Yes

Key Moments - 3 Insights

Why does the AFTER trigger run only after the data is inserted?

Because AFTER triggers execute after the DML operation completes successfully, as shown in execution_table step 2 where the trigger fires only after the employee row exists.

Can the AFTER trigger modify the same table it is triggered on?

Usually no, because the original operation has completed and the trigger runs after. Modifying the same table can cause recursion or errors. The example shows it modifies a different table (audit_log).

What happens if the trigger code fails?

If the trigger fails, the entire original DML operation is rolled back. This is implied because the trigger runs within the transaction after the DML step.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the trigger actually insert into audit_log?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Check the 'Trigger Action' column in execution_table rows.

According to variable_tracker, how many rows are in employees after Step 1?

A 0

B 1

C 2

D Not changed

💡 Hint

Look at 'employees rows' after Step 1 in variable_tracker.

If the trigger code failed at Step 2, what would happen to the employees table?

A The new employee row remains

B Only audit_log is rolled back

C The new employee row is rolled back

D Nothing changes

💡 Hint

Recall that triggers run inside the transaction and failure rolls back the whole operation.

Concept Snapshot

AFTER triggers run after a data change (INSERT, UPDATE, DELETE) completes.
They execute trigger code after the row is affected.
They can modify other tables but usually not the triggering table.
If trigger fails, the whole transaction rolls back.
Used for logging, auditing, or enforcing rules post-change.

Full Transcript

An AFTER trigger in SQL runs after a data modification operation like INSERT, UPDATE, or DELETE completes successfully on a table. For example, when a new employee is inserted, the AFTER INSERT trigger fires and executes its code, such as inserting a record into an audit_log table. The trigger runs inside the same transaction as the original operation, so if the trigger code fails, the entire transaction including the original insert is rolled back. The execution flow starts with the data change, then the trigger fires, executes its code, completes, and finally the original transaction commits. Variables like table rows and trigger state change step-by-step as shown in the execution table and variable tracker. This helps ensure data integrity and allows actions like auditing after data changes.