Why should you care?

When order status flips to SHIPPED, warehouse notification row must appear — AFTER INSERT on status_history is reliable.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TRIGGER trg_orders_shipped_notify
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.status = 'SHIPPED' AND OLD.status <> 'SHIPPED' THEN
    INSERT INTO shipment_queue (order_id, queued_at)
    VALUES (NEW.order_id, NOW());
  END IF;
END;

What happened?

• Trigger watches status transition to SHIPPED.
• Writes shipment_queue row for downstream worker.
• AFTER timing means order row already committed when queue insert runs (same txn).

Practice next

1. ADD status VARCHAR(20) to orders; CREATE shipment_queue.
2. CREATE AFTER UPDATE trigger.
3. UPDATE status to SHIPPED; check queue.
4. Log cancelled orders to cancel_audit on status CANCELLED.
5. Combine with BEFORE trigger for validate + notify pattern.

Remember

AFTER = post-change side effects. Compare OLD/NEW for meaningful transitions. Keep trigger body fast.

DataFlow ship webhook

Worker polls shipment_queue populated by AFTER trigger — courier API called.

Outcome: Shipping starts even if Node app forgot hook.