Complex trigger with transaction-safe stock check
SQL
Hard
0 views
Problem Description
Before inserting OrderItems, ensure Inventory has enough qty for that product. If stock is not enough, raise an error to stop the insert.
Output Format
Trigger created
Sample Test Case
Input:
OrderItems, Inventory
Constraints
MySQL-style SELECT ... INTO variable
Official Solution
CREATE TRIGGER trg_check_stock BEFORE INSERT ON OrderItems FOR EACH ROW BEGIN DECLARE available INT; SELECT qty INTO available FROM Inventory WHERE product_id = NEW.product_id; IF available IS NULL OR available < NEW.qty THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Insufficient stock'; END IF; END;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!