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.

Practice

1. What is the main purpose of an AFTER trigger in SQL?

easy

A. To execute actions only after a successful data change like insert, update, or delete

B. To prevent data changes before they happen

C. To rollback transactions automatically

D. To create new tables after data insertion

Solution

1. Step 1: Understand the timing of AFTER triggers

   AFTER triggers run only after the main data change operation completes successfully.

2. Step 2: Identify the purpose of AFTER triggers

   They automate tasks like logging or updating related data after changes, ensuring consistency.

3. Final Answer:

   To execute actions only after a successful data change like insert, update, or delete -> Option A

4. Quick Check:

   AFTER trigger = post-change action [OK]

Hint: AFTER triggers run only after successful data changes [OK]

Common Mistakes:

• Confusing BEFORE and AFTER triggers
• Thinking AFTER triggers run before data changes
• Assuming AFTER triggers rollback transactions

2. Which of the following is the correct syntax to create an AFTER INSERT trigger in SQL?

easy

A. CREATE TRIGGER trg ON table_name AFTER DELETE BEGIN ... END;

B. CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END;

C. CREATE TRIGGER trg BEFORE INSERT ON table_name BEGIN ... END;

D. CREATE AFTER TRIGGER trg ON table_name INSERT BEGIN ... END;

Solution

1. Step 1: Recall standard AFTER trigger syntax

   The correct syntax starts with CREATE TRIGGER, followed by trigger name, timing (AFTER), event (INSERT), and table name.

2. Step 2: Check each option

   CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END; matches the correct syntax. Options A, B, and C have wrong order, timing, or event.

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name BEGIN ... END; -> Option B

4. Quick Check:

   CREATE TRIGGER + AFTER + INSERT = correct syntax [OK]

Hint: AFTER triggers syntax: CREATE TRIGGER name AFTER event ON table [OK]

Common Mistakes:

• Mixing BEFORE and AFTER keywords
• Placing AFTER in wrong position
• Using DELETE event instead of INSERT

3. Given this trigger and table:

CREATE TABLE sales (id INT, amount INT);
CREATE TRIGGER trg AFTER INSERT ON sales
BEGIN
  INSERT INTO audit_log VALUES ('insert', NEW.id, NEW.amount);
END;

What happens when you run INSERT INTO sales VALUES (1, 100);?

medium

A. New rows are added to both sales and audit_log

B. A new row is added to audit_log only

C. A new row is added to sales only

D. No rows are added due to trigger error

Solution

1. Step 1: Understand the trigger action

   The AFTER INSERT trigger inserts a row into audit_log using the new sales row data.

2. Step 2: Analyze the insert command effect

   Inserting into sales triggers the AFTER INSERT, so both sales and audit_log get new rows.

3. Final Answer:

   New rows are added to both sales and audit_log -> Option A

4. Quick Check:

   AFTER INSERT triggers run after insert, adding audit_log row [OK]

Hint: AFTER INSERT triggers run after insert, affecting related tables [OK]

Common Mistakes:

• Thinking trigger replaces original insert
• Assuming trigger runs before insert
• Ignoring NEW keyword usage

4. You wrote this AFTER UPDATE trigger:

CREATE TRIGGER trg AFTER UPDATE ON employees
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE id = NEW.id;
END;

What problem will occur when you update an employee's salary?

medium

A. Salary will not change

B. Trigger will not run at all

C. Syntax error prevents trigger creation

D. Infinite loop causing stack overflow

Solution

1. Step 1: Analyze trigger logic

   The trigger updates the same table it is triggered on, causing the trigger to fire again.

2. Step 2: Identify the effect of recursive updates

   This causes an infinite loop of updates, eventually causing stack overflow or error.

3. Final Answer:

   Infinite loop causing stack overflow -> Option D

4. Quick Check:

   Trigger updating same table without stop = infinite loop [OK]

Hint: Avoid triggers updating same table without condition to stop recursion [OK]

Common Mistakes:

• Not realizing trigger recursion
• Expecting trigger to run once only
• Ignoring NEW and OLD references

5. You want to log every DELETE from the orders table into orders_log only if the delete succeeds. Which is the best way to do this using an AFTER trigger?

hard

A. Create an AFTER INSERT trigger on orders_log that deletes from orders

B. Create a BEFORE DELETE trigger that inserts into orders_log before deletion

C. Create an AFTER DELETE trigger on orders that inserts deleted row info into orders_log

D. Create a trigger that deletes from orders_log after orders delete

Solution

1. Step 1: Understand timing for logging deletes

   AFTER DELETE triggers run only after the delete succeeds, ensuring accurate logging.

2. Step 2: Choose correct trigger type

   Creating an AFTER DELETE trigger on orders to insert into orders_log captures deleted data reliably.

3. Final Answer:

   Create an AFTER DELETE trigger on orders that inserts deleted row info into orders_log -> Option C

4. Quick Check:

   AFTER DELETE trigger logs only successful deletions [OK]

Hint: Use AFTER DELETE triggers to log only successful deletions [OK]

Common Mistakes:

• Using BEFORE DELETE triggers which may log failed deletes
• Confusing INSERT triggers with DELETE triggers
• Trying to delete from log table after orders delete