You want to create a BEFORE UPDATE trigger on the products table that prevents the price from being set below zero. Which trigger function code correctly enforces this rule?
hard📝 Application Q15 of 15
PostgreSQL - Triggers in PostgreSQL
You want to create a BEFORE UPDATE trigger on the products table that prevents the price from being set below zero. Which trigger function code correctly enforces this rule?
A<pre>BEGIN
IF NEW.price < 0 THEN
NEW.price := 0;
END IF;
RETURN NEW;
END;</pre>
B<pre>BEGIN
IF NEW.price < 0 THEN
RAISE EXCEPTION 'Price cannot be negative';
END IF;
RETURN NEW;
END;</pre>
C<pre>BEGIN
IF OLD.price < 0 THEN
RAISE EXCEPTION 'Price cannot be negative';
END IF;
RETURN NEW;
END;</pre>
D<pre>BEGIN
IF NEW.price < 0 THEN
DELETE FROM products WHERE id = NEW.id;
END IF;
RETURN NEW;
END;</pre>
Step-by-Step Solution
Solution:
Step 1: Identify correct condition check
The trigger must check NEW.price to prevent negative values before update.
Step 2: Choose proper action on invalid data
Raising an exception stops the update and prevents invalid price.
Step 3: Eliminate incorrect options
BEGIN
IF NEW.price < 0 THEN
NEW.price := 0;
END IF;
RETURN NEW;
END;
silently changes price to 0 (may hide errors), C checks OLD.price (wrong), D deletes row (not appropriate).
Final Answer:
BEGIN
IF NEW.price < 0 THEN
RAISE EXCEPTION 'Price cannot be negative';
END IF;
RETURN NEW;
END; -> Option B
Quick Check:
Use RAISE EXCEPTION on NEW.price < 0 to stop update [OK]
Quick Trick:Raise error on NEW.price < 0 to block update [OK]
Common Mistakes:
Checking OLD.price instead of NEW.price
Silently fixing invalid data instead of error
Deleting rows inside BEFORE trigger
Master "Triggers in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
