Why should you care?

Audit trail on orders — every total change logged without trusting every developer to remember in app code.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE order_audit (
  audit_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id INT UNSIGNED NOT NULL,
  old_total DECIMAL(12,2),
  new_total DECIMAL(12,2),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_orders_total_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.total_inr <> NEW.total_inr THEN
    INSERT INTO order_audit (order_id, old_total, new_total)
    VALUES (NEW.order_id, OLD.total_inr, NEW.total_inr);
  END IF;
END;

What happened?

• After orders.total_inr updates, trigger compares OLD vs NEW and writes audit row.
• Apps stay thin; database guarantees log on any path that touches the table.

Practice next

1. Create order_audit and trigger.
2. UPDATE orders SET total_inr = total_inr + 10 WHERE order_id = 1;
3. SELECT * FROM order_audit;
4. Add INSERT trigger logging new orders.
5. DROP TRIGGER trg_orders_total_audit when refactoring to app audit.

Remember

Triggers react to DML automatically. OLD/NEW hold row before/after images. Use for audit and derived enforcement.

DataFlow compliance log

RBI audit asks who changed loan order amounts — order_audit answers.

Outcome: No manual logging code in twelve microservices.