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.

Practice

1. What is the main purpose of a DELETE trigger in a database?

easy

A. To insert new rows into a table

B. To update rows before they are deleted

C. To automatically run code when rows are deleted from a table

D. To prevent any data from being deleted

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically in response to certain database events like insert, update, or delete.

2. Step 2: Focus on DELETE triggers

   A DELETE trigger specifically runs when rows are deleted, allowing automatic actions during that event.

3. Final Answer:

   To automatically run code when rows are deleted from a table -> Option C

4. Quick Check:

   DELETE trigger = runs on row deletion [OK]

Hint: DELETE triggers run code automatically on row deletions [OK]

Common Mistakes:

• Confusing DELETE triggers with INSERT triggers
• Thinking DELETE triggers prevent deletion by default
• Assuming DELETE triggers update rows instead

2. Which of the following is the correct syntax to create a trigger that runs before a row is deleted from a table named Employees?

easy

A. CREATE TRIGGER before_delete_employee DELETE ON Employees FOR EACH ROW BEGIN --code END;

B. CREATE TRIGGER before_delete_employee AFTER DELETE ON Employees FOR EACH ROW BEGIN --code END;

C. CREATE TRIGGER before_delete_employee ON Employees BEFORE DELETE FOR EACH ROW BEGIN --code END;

D. CREATE TRIGGER before_delete_employee BEFORE DELETE ON Employees FOR EACH ROW BEGIN --code END;

Solution

1. Step 1: Identify correct trigger timing syntax

   The syntax for a BEFORE DELETE trigger includes 'BEFORE DELETE ON TableName'.

2. Step 2: Check full syntax correctness

   CREATE TRIGGER before_delete_employee BEFORE DELETE ON Employees FOR EACH ROW BEGIN --code END; correctly uses 'CREATE TRIGGER name BEFORE DELETE ON Employees FOR EACH ROW BEGIN ... END;'. Others misuse order or keywords.

3. Final Answer:

   CREATE TRIGGER before_delete_employee BEFORE DELETE ON Employees FOR EACH ROW BEGIN --code END; -> Option D

4. Quick Check:

   BEFORE DELETE ON Table = correct syntax [OK]

Hint: Use BEFORE DELETE ON table for pre-delete triggers [OK]

Common Mistakes:

• Placing BEFORE DELETE after ON table
• Omitting FOR EACH ROW clause
• Using DELETE ON instead of BEFORE DELETE ON

3. Given the following trigger and table data, what will be the output after deleting the row with id = 2 from Orders?

CREATE TRIGGER log_delete AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO DeletedOrdersLog(order_id, deleted_at) VALUES (OLD.id, CURRENT_TIMESTAMP);
END;

-- Initial Orders table:
-- id | product
-- 1  | Book
-- 2  | Pen
-- 3  | Notebook

-- DeletedOrdersLog is initially empty.

medium

A. A new row with order_id = 2 is inserted into DeletedOrdersLog

B. The row with id = 2 is restored in Orders

C. No changes happen to DeletedOrdersLog

D. An error occurs because OLD.id is invalid

Solution

1. Step 1: Understand AFTER DELETE trigger behavior

   The trigger runs after a row is deleted from Orders, so OLD.id refers to the deleted row's id.

2. Step 2: Analyze trigger action

   The trigger inserts a new row into DeletedOrdersLog with order_id = OLD.id (2) and current timestamp.

3. Final Answer:

   A new row with order_id = 2 is inserted into DeletedOrdersLog -> Option A

4. Quick Check:

   AFTER DELETE inserts log row with OLD.id [OK]

Hint: AFTER DELETE triggers can log deleted row info using OLD [OK]

Common Mistakes:

• Thinking OLD.id is not accessible in DELETE triggers
• Assuming trigger restores deleted rows
• Believing no action happens after delete

4. Consider this trigger code snippet intended to prevent deletion of rows from Users table if the user is an admin:

CREATE TRIGGER prevent_admin_delete BEFORE DELETE ON Users
FOR EACH ROW
BEGIN
  IF OLD.role = 'admin' THEN
    SIGNAL SQLSTATE '45000' MESSAGE_TEXT = 'Cannot delete admin user';
  END IF;
END;

What is the main issue if this trigger does not prevent admin deletions?

medium

A. The SIGNAL statement syntax is incorrect

B. The condition should check NEW.role instead of OLD.role

C. The trigger does not specify FOR EACH ROW

D. The trigger should be AFTER DELETE, not BEFORE DELETE

Solution

1. Step 1: Check trigger timing and row context

   BEFORE DELETE and FOR EACH ROW are correctly used to check OLD.role before deletion.

2. Step 2: Verify SIGNAL syntax

   In standard SQL, SIGNAL syntax requires correct SQLSTATE and message format. If syntax is wrong, error won't raise and deletion proceeds.

3. Final Answer:

   The SIGNAL statement syntax is incorrect -> Option A

4. Quick Check:

   SIGNAL syntax error prevents trigger from blocking delete [OK]

Hint: Check SIGNAL syntax carefully to block deletes [OK]

Common Mistakes:

• Using AFTER DELETE instead of BEFORE DELETE
• Checking NEW instead of OLD in DELETE triggers
• Omitting FOR EACH ROW clause

5. You want to create a DELETE trigger on a Products table that archives deleted rows into an ArchivedProducts table, but only if the product price is above 100. Which trigger definition correctly implements this logic?

hard

A. CREATE TRIGGER archive_expensive_product BEFORE DELETE ON Products FOR EACH ROW BEGIN IF NEW.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (NEW.id, NEW.price); END IF; END;

B. CREATE TRIGGER archive_expensive_product AFTER DELETE ON Products FOR EACH ROW BEGIN IF OLD.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (OLD.id, OLD.price); END IF; END;

C. CREATE TRIGGER archive_expensive_product AFTER DELETE ON Products FOR EACH ROW BEGIN IF NEW.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (OLD.id, OLD.price); END IF; END;

D. CREATE TRIGGER archive_expensive_product BEFORE DELETE ON Products FOR EACH ROW BEGIN IF NEW.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (OLD.id, OLD.price); END IF; END;

Solution

1. Step 1: Choose correct timing and row reference

   AFTER DELETE triggers run after deletion; OLD refers to deleted row data. BEFORE DELETE can also work but OLD is preferred after deletion.

2. Step 2: Check condition and insert logic

   Condition must check OLD.price > 100. Insert must use OLD column values to archive deleted row data.

3. Step 3: Validate options

   CREATE TRIGGER archive_expensive_product AFTER DELETE ON Products FOR EACH ROW BEGIN IF OLD.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (OLD.id, OLD.price); END IF; END; uses AFTER DELETE, OLD.price, and inserts OLD.id, OLD.price correctly. Others use NEW or wrong timing.

4. Final Answer:

   CREATE TRIGGER archive_expensive_product AFTER DELETE ON Products FOR EACH ROW BEGIN IF OLD.price > 100 THEN INSERT INTO ArchivedProducts (id, price) VALUES (OLD.id, OLD.price); END IF; END; -> Option B

5. Quick Check:

   AFTER DELETE + OLD.price condition + insert OLD columns = correct [OK]

Hint: Use AFTER DELETE and OLD to archive deleted rows conditionally [OK]

Common Mistakes:

• Using NEW instead of OLD in DELETE triggers
• Placing condition on NEW.price which is invalid
• Using BEFORE DELETE but referencing OLD incorrectly