INSERT trigger in SQL - Step-by-Step Execution

Concept Flow - INSERT trigger

New row inserted into table

↓

Trigger fires automatically

↓

Trigger code executes

↓

Trigger can modify data, log, or enforce rules

↓

Insert operation completes

When a new row is inserted, the INSERT trigger runs automatically to perform extra actions before or after the insert.

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 adds a record to audit_log every time a new employee is inserted.

Execution Table

Step	Action	Table State Before	Trigger Fired?	Trigger Action	Table State After
1	Insert new employee (id=101, name='Alice')	employees: empty audit_log: empty	Yes	Insert (101, 'Inserted') into audit_log	employees: (101, 'Alice') audit_log: (101, 'Inserted')
2	Insert new employee (id=102, name='Bob')	employees: (101, 'Alice') audit_log: (101, 'Inserted')	Yes	Insert (102, 'Inserted') into audit_log	employees: (101, 'Alice'), (102, 'Bob') audit_log: (101, 'Inserted'), (102, 'Inserted')
3	No more inserts	employees: (101, 'Alice'), (102, 'Bob') audit_log: (101, 'Inserted'), (102, 'Inserted')	No	No action	No change

💡 No more insert operations, trigger does not fire.

Variable Tracker

Variable	Start	After 1	After 2	Final
employees	empty	(101, 'Alice')	(101, 'Alice'), (102, 'Bob')	(101, 'Alice'), (102, 'Bob')
audit_log	empty	(101, 'Inserted')	(101, 'Inserted'), (102, 'Inserted')	(101, 'Inserted'), (102, 'Inserted')

Key Moments - 3 Insights

Why does the trigger fire after each insert and not just once?

Because the trigger is defined FOR EACH ROW, it runs once for every inserted row, as shown in execution_table rows 1 and 2.

Can the trigger modify the inserted row before it is saved?

No, this trigger is AFTER INSERT, so it runs after the row is saved. To modify before saving, a BEFORE INSERT trigger is needed.

What happens if the trigger code fails during execution?

The entire insert operation fails and rolls back, so no partial data is saved. This ensures data consistency.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of audit_log after step 2?

A empty

B (101, 'Alice'), (102, 'Bob')

C (101, 'Inserted'), (102, 'Inserted')

D (102, 'Inserted')

💡 Hint

Check the 'Table State After' column in row 2 of execution_table.

At which step does the trigger NOT fire?

A Step 3

B Step 1

C Step 2

D Trigger fires every step

💡 Hint

Look at the 'Trigger Fired?' column in execution_table.

If the trigger was BEFORE INSERT instead of AFTER INSERT, what would change?

A Trigger would not run at all

B Trigger would run before the row is saved, allowing modification of NEW row data

C Trigger would run after the insert and could not modify data

D Trigger would run only once for all rows

💡 Hint

Consider the timing of BEFORE vs AFTER triggers in the key_moments section.

Concept Snapshot

INSERT trigger runs automatically when a new row is added.
Defined FOR EACH ROW to act on every inserted row.
Can run BEFORE or AFTER insert to modify data or log actions.
Useful for auditing, enforcing rules, or cascading changes.
Trigger failure rolls back the insert to keep data safe.

Full Transcript

An INSERT trigger is a special database feature that runs automatically when a new row is added to a table. It can run before or after the insert operation. In this example, the trigger runs after each new employee is inserted and adds a record to an audit_log table. The execution table shows each insert step, the trigger firing, and the resulting table states. Variables track the employees and audit_log contents after each insert. Key moments clarify why the trigger fires for each row, when it can modify data, and what happens if it fails. The visual quiz tests understanding of trigger timing and effects. Overall, INSERT triggers help automate tasks like logging and data validation during inserts.