Stop duplicate active coupon
SQL
Medium
0 views
Problem Description
Coupons(code, is_active). Before INSERT, if the same code already exists with is_active=1, then block the insert.
Output Format
Trigger created
Constraints
MySQL-style SIGNAL + EXISTS
Official Solution
CREATE TRIGGER trg_coupon_unique_active BEFORE INSERT ON Coupons FOR EACH ROW BEGIN IF NEW.is_active = 1 AND EXISTS (SELECT 1 FROM Coupons c WHERE c.code = NEW.code AND c.is_active = 1) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Active coupon already exists'; END IF; END;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!