A company wants to ensure that whenever a product's price is updated, the old price is saved in a PriceHistory table automatically. Which trigger design best achieves this?
hard📝 Application Q15 of 15
SQL - Triggers
A company wants to ensure that whenever a product's price is updated, the old price is saved in a PriceHistory table automatically. Which trigger design best achieves this?
AA BEFORE DELETE trigger on Products that inserts old price into PriceHistory
BAn AFTER INSERT trigger on Products that inserts new price into PriceHistory
CAn AFTER UPDATE trigger on Products that inserts old price into PriceHistory
DA BEFORE UPDATE trigger on Products that inserts old price into PriceHistory
Step-by-Step Solution
Solution:
Step 1: Understand when old price is available
The old price is available after the update operation starts but before the new data replaces it, so a trigger on UPDATE is needed.
Step 2: Choose trigger timing to save old price
Using AFTER UPDATE ensures the update succeeded, and the trigger can insert the old price (accessible via OLD) into PriceHistory.
Final Answer:
An AFTER UPDATE trigger on Products that inserts old price into PriceHistory -> Option C
Quick Check:
Save old price after update = A [OK]
Quick Trick:Use AFTER UPDATE trigger to save old data changes [OK]
Common Mistakes:
Using BEFORE DELETE instead of UPDATE
Using AFTER INSERT which doesn't track price changes
Choosing BEFORE UPDATE which may block or cause errors
Master "Triggers" in SQL
9 interactive learning modes - each teaches the same concept differently
