[Solved] Consider this trigger for audit logging deletions:CREATE TRIGGER audit_delete BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO... END;What will be the effect of this trigger? | SQL

SQL - Triggers

Consider this trigger for audit logging deletions:

CREATE TRIGGER audit_delete BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO audit_log(order_id, deleted_at) VALUES (OLD.id, NOW()); END;

What will be the effect of this trigger?

AIt will fail because BEFORE DELETE triggers cannot access OLD values

BIt will log the order ID and deletion time before the row is deleted

CIt will log the order ID but the timestamp will be NULL

DIt will log after the row is deleted, so OLD.id will be unavailable

Step-by-Step Solution

Solution:

Step 1: Trigger timing
BEFORE DELETE triggers fire before the row is removed, so OLD values are accessible.
Step 2: Accessing OLD values
OLD.id is valid in BEFORE DELETE triggers.
Step 3: Timestamp function
NOW() returns current timestamp correctly.
Final Answer:
It will log the order ID and deletion time before the row is deleted -> Option B
Quick Check:
BEFORE DELETE can access OLD data [OK]

Quick Trick: BEFORE DELETE triggers can use OLD values [OK]

Common Mistakes:

Master "Triggers" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Consider this trigger for audit logging deletions: