You want to ensure that before inserting a new order, the quantity is never negative. Which trigger code correctly enforces this using a BEFORE INSERT trigger?
hard📝 Application Q15 of 15
SQL - Triggers
You want to ensure that before inserting a new order, the quantity is never negative. Which trigger code correctly enforces this using a BEFORE INSERT trigger?
ACREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END;
BCREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;
CCREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;
DCREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END;
Step-by-Step Solution
Solution:
Step 1: Identify correct timing and event
We want to check before inserting, so BEFORE INSERT is correct.
Step 2: Check enforcement method
To stop negative quantity, raising an error with SIGNAL is best. CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE INSERT and SIGNAL correctly.
Step 3: Eliminate wrong options
CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SET NEW.quantity = 0; END IF; END; silently changes quantity, not enforcing. CREATE TRIGGER trg AFTER INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses AFTER INSERT, too late to stop insert. CREATE TRIGGER trg BEFORE UPDATE ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; uses BEFORE UPDATE, not insert.
Final Answer:
CREATE TRIGGER trg BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.quantity < 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative quantity not allowed'; END IF; END; -> Option B
Quick Check:
Use BEFORE INSERT + SIGNAL to block invalid data [OK]
Quick Trick:Use BEFORE INSERT with SIGNAL to block bad data [OK]
Common Mistakes:
Using AFTER INSERT instead of BEFORE INSERT
Silently fixing data instead of raising error
Checking on UPDATE instead of INSERT
Master "Triggers" in SQL
9 interactive learning modes - each teaches the same concept differently
