Attach an AFTER INSERT trigger on order_lines that subtracts NEW.qty from products.stock. Then insert order 3 for Ada buying one MUG-9 at 900 cents, and return mug stock plus the new line. Do not replay the trigger on the historical PEN-2 line: create the trigger after the seed.
Requirements
Keep the seed customers, products, orders, and the historical PEN-2 line.
CREATE TRIGGER trg_dec_stock AFTER INSERT ON order_lines that updates products.stock by subtracting NEW.qty for NEW.sku.
INSERT order 3 for customer 1, then INSERT the mug line (3, 'MUG-9', 1, 900).
Return sku and stock for MUG-9.
Then return order_id, sku, qty for order 3.
PEN-2 stock must remain 2 because the trigger did not exist during seed.
Example
Input
Create the trigger after seed, insert the mug line, then select stock and the new line.
CREATE TRIGGER after the seed INSERT. Body: UPDATE products SET stock = stock - NEW.qty WHERE sku = NEW.sku. Then insert order 3 and the mug line, then the two SELECTs.
A trigger is a reaction, not a procedure you CALL. AFTER INSERT ON order_lines runs once per new line and sees NEW.qty and NEW.sku. Creating it after the historical pen line is required: if the trigger existed during seed, PEN-2 would already be 1 and the mug demo would be harder to trust. The mug starts at 8; qty 1 leaves 7. The runner concatenates both SELECT result sets, so the test can see stock and the line together. Foreign keys stay on so order 3 must exist before its line. In an app you would also wrap the header and line in BEGIN/COMMIT; this exercise isolates the trigger contract.
Why this pattern matters
Stock must move with the line even when someone inserts from a console. A trigger after seed is how you teach the reaction without rewriting history.
Solution walkthrough
Leave the PEN-2 seed insert before CREATE TRIGGER.
AFTER INSERT UPDATE products SET stock = stock - NEW.qty.
Insert order 3 then the mug line.
Select mug stock 7 and the new line.
Common mistakes to avoid
Creating the trigger first so PEN-2 becomes 1.
Updating all products instead of WHERE sku = NEW.sku.
Forgetting the orders row and failing the foreign key.
What this exercise teaches
Create an AFTER INSERT trigger
Use NEW to read the incoming line
Keep seed history from firing twice
A PRACTICAL PLAN
Work through Decrement stock when an order line lands with intent.
Translate the contract.Turn the requirements into a short checklist before editing your-solution.sql.
Use the example as evidence.Predict the result for the supplied input, then add one boundary case such as an empty value, a limit, or unexpected input.
Review the implementation.Compare your choices against solution.sql only after a real attempt.
EXERCISE FAQ
Before you move on.
What does “Decrement stock when an order line lands” teach?
This advanced SQL exercise focuses on Create an AFTER INSERT trigger, Use NEW to read the incoming line, Keep seed history from firing twice. Its requirements define the exact behavior to implement before you write code.
How should I validate this SQL solution?
Start with the displayed example input and expected output, then test a boundary case suggested by the requirements. Open your-solution.sql and select Run code. The browser creates a fresh isolated SQLite database, executes the complete script, and compares its result rows with the expected report.
When should I open the reference solution?
Attempt Decrement stock when an order line lands first. Then open the read-only solution file to compare the contract, edge-case handling, and implementation choices—not simply to copy the final code.
