DELETE trigger in SQL - Step-by-Step Execution

Concept Flow - DELETE trigger

DELETE command issued

↓

Trigger activated BEFORE or AFTER DELETE

↓

Trigger code executes

↓

DELETE operation completes

↓

Transaction commits or rolls back

When a DELETE command runs, the DELETE trigger activates before or after the row is removed, running its code to react or enforce rules.

Execution Sample

SQL

CREATE TRIGGER trg_before_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (OLD.id, 'Deleted');
END;

This trigger logs the id of each deleted employee before the row is removed.

Execution Table

Step	Action	Trigger Event	Trigger Code Execution	Result
1	DELETE FROM employees WHERE id=3;	BEFORE DELETE	Insert OLD.id=3 into audit_log	Row with id=3 marked for deletion
2	Trigger code runs	BEFORE DELETE	Audit log updated with id=3	Audit log has new entry for deleted id=3
3	Row deleted from employees	DELETE operation	No trigger code	Row with id=3 removed from employees
4	Transaction commits	Post DELETE	No trigger code	Deletion finalized

💡 DELETE completes after trigger code runs and row is removed

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
OLD.id	N/A	3	3	3	N/A (row deleted)
audit_log entries	empty	empty	contains id=3 entry	contains id=3 entry	contains id=3 entry
employees rows	contains id=3	contains id=3	contains id=3	id=3 removed	id=3 removed

Key Moments - 3 Insights

Why can we use OLD.id in the trigger code?

Because DELETE triggers have access to the OLD row data before it is deleted, as shown in execution_table step 2 where OLD.id=3 is used.

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

In this example, the trigger is BEFORE DELETE, so it runs before the row is removed, as seen in step 2 before step 3 deletes the row.

What happens if the trigger code fails?

If the trigger code fails, the DELETE operation is rolled back and the row is not deleted, ensuring data integrity.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of OLD.id during the trigger execution?

A The new id

B NULL

C 3

D Not accessible

💡 Hint

Check the 'Trigger Code Execution' column in step 2 where OLD.id=3 is used.

At which step is the employee row actually removed from the table?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Result' column to see when the row is removed.

If the trigger was AFTER DELETE instead of BEFORE DELETE, when would the audit_log be updated?

A Before the row is deleted

B After the row is deleted

C At the same time as deletion

D It would not run

💡 Hint

Consider the trigger event timing and when the code runs relative to deletion.

Concept Snapshot

DELETE trigger syntax:
CREATE TRIGGER name BEFORE|AFTER DELETE ON table
FOR EACH ROW BEGIN ... END;

- Runs when a row is deleted
- Access OLD row data
- Can run before or after deletion
- Useful for logging or enforcing rules

Full Transcript

A DELETE trigger runs automatically when a DELETE command removes rows from a table. It can run before or after the row is deleted. The trigger code can access the OLD row data to perform actions like logging. In the example, a BEFORE DELETE trigger inserts the deleted row's id into an audit log before the row is removed. The execution steps show the DELETE command triggers the code, which updates the audit log, then the row is deleted, and finally the transaction commits. Variables like OLD.id hold the deleted row's data during trigger execution. If the trigger fails, the deletion is rolled back to keep data safe.