Which trigger definition correctly implements this logic?
hard📝 Application Q15 of 15
SQL - Triggers
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?
ACREATE 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;
BCREATE 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;
CCREATE 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;
DCREATE 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;
Step-by-Step Solution
Solution:
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.
Step 2: Check condition and insert logic
Condition must check OLD.price > 100. Insert must use OLD column values to archive deleted row data.
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.
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
Quick Check:
AFTER DELETE + OLD.price condition + insert OLD columns = correct [OK]
Quick Trick: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
Master "Triggers" in SQL
9 interactive learning modes - each teaches the same concept differently
