AFTER INSERT triggers in MySQL - Step-by-Step Execution

Concept Flow - AFTER INSERT triggers

New row inserted into table

↓

AFTER INSERT trigger fires

↓

Trigger executes its statements

↓

Trigger completes

↓

Insert operation completes

After a new row is inserted into a table, the AFTER INSERT trigger automatically runs its defined actions before the insert operation finishes.

Execution Sample

MySQL

CREATE TRIGGER log_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (emp_id, action) VALUES (NEW.id, 'inserted');
END;

This trigger logs each new employee inserted into the employees table by adding a record to audit_log.

Execution Table

Step	Action	Table State	Trigger Action	Trigger Effect
1	Insert new employee row (id=101, name='Alice')	employees: +1 row (id=101, name='Alice')	Trigger fires AFTER INSERT	Prepare to insert audit log record
2	Trigger inserts audit log row (emp_id=101, action='inserted')	audit_log: +1 row (emp_id=101, action='inserted')	Trigger executes INSERT	Audit log updated
3	Trigger completes	No change	Trigger ends	Insert operation completes
4	Insert operation completes	employees and audit_log updated	No trigger	Final state committed

💡 Trigger runs after each insert row; stops after executing its statements and insert completes

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
NEW.id	undefined	101	101	101
NEW.name	undefined	Alice	Alice	Alice
audit_log rows	0	0	1	1
employees rows	0	1	1	1

Key Moments - 3 Insights

Why does the trigger use NEW.id instead of just id?

The trigger uses NEW.id to refer to the inserted row's id value; this is shown in execution_table step 2 where NEW.id=101 is used to insert into audit_log.

Does the trigger run before or after the row is inserted?

The trigger runs after the row is inserted, as shown in execution_table step 1 where the employee row is added before the trigger fires.

Can the trigger modify the inserted row?

No, AFTER INSERT triggers cannot change the inserted row; they only run after insertion, shown by no changes to employees after trigger execution.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of NEW.id when the trigger inserts into audit_log?

A NULL

B undefined

C 101

D 0

💡 Hint

Check execution_table row 2 under 'Trigger Action' and 'Trigger Effect' columns

At which step does the trigger finish its execution?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at execution_table row 3 where it says 'Trigger ends'

If the trigger was BEFORE INSERT instead of AFTER INSERT, when would it run?

A Before the row is inserted

B After the row is inserted

C After the trigger completes

D It would not run

💡 Hint

Compare the concept_flow diagram showing AFTER INSERT timing

Concept Snapshot

AFTER INSERT triggers run automatically after a new row is added.
They can perform actions like logging or updating other tables.
Use NEW.column to access inserted row values.
They cannot modify the inserted row.
Trigger runs once per inserted row after insertion completes.

Full Transcript

An AFTER INSERT trigger in MySQL runs automatically after a new row is inserted into a table. When you insert a row, the trigger fires and executes its statements, such as inserting a log record into another table. The trigger can access the inserted row's data using NEW.column syntax. It cannot change the inserted row itself because it runs after insertion. The insert operation completes only after the trigger finishes. This helps automate tasks like auditing or cascading actions after data is added.