Triggers

-- 1) Updated-at touch — common pattern, but Postgres has no built-in shortcut
CREATE OR REPLACE FUNCTION trg_set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_set_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION trg_set_updated_at();

-- 2) Append-only audit log of every change
CREATE TABLE orders_audit (
  id           bigserial PRIMARY KEY,
  order_id     bigint NOT NULL,
  op           text NOT NULL,        -- INSERT / UPDATE / DELETE
  changed_by   text,
  changed_at   timestamptz DEFAULT now(),
  before       jsonb,
  after        jsonb
);

CREATE OR REPLACE FUNCTION trg_orders_audit()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE who text;
BEGIN
  who := current_setting('app.user_id', true);   -- set by app: SELECT set_config('app.user_id', '42', true);
  IF TG_OP = 'DELETE' THEN
    INSERT INTO orders_audit(order_id, op, changed_by, before)
      VALUES (OLD.id, 'DELETE', who, to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO orders_audit(order_id, op, changed_by, before, after)
      VALUES (NEW.id, 'UPDATE', who, to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSE
    INSERT INTO orders_audit(order_id, op, changed_by, after)
      VALUES (NEW.id, 'INSERT', who, to_jsonb(NEW));
    RETURN NEW;
  END IF;
END;
$$;

CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION trg_orders_audit();

-- 3) Enforce a domain invariant the app could violate
CREATE OR REPLACE FUNCTION trg_orders_status_guard()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'shipped' AND OLD.status NOT IN ('paid', 'shipped') THEN
    RAISE EXCEPTION 'cannot ship from status %', OLD.status;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_status_guard
  BEFORE UPDATE OF status ON orders
  FOR EACH ROW
  WHEN (NEW.status IS DISTINCT FROM OLD.status)
  EXECUTE FUNCTION trg_orders_status_guard();

-- 4) Conditional cascade — when an order is cancelled, void the invoice
CREATE OR REPLACE FUNCTION trg_orders_cascade_cancel()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'cancelled' AND OLD.status <> 'cancelled' THEN
    UPDATE invoices SET status = 'void' WHERE order_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_cascade_cancel
  AFTER UPDATE OF status ON orders
  FOR EACH ROW
  EXECUTE FUNCTION trg_orders_cascade_cancel();

-- 5) Statement-level trigger — fire ONCE per statement, not per row
-- Use for stats / housekeeping where you do not need per-row data
CREATE OR REPLACE FUNCTION trg_orders_stats()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO ops_log(at, action)
    VALUES (now(), 'orders changed: ' || TG_OP);
  RETURN NULL;        -- ignored for AFTER-statement triggers
END;
$$;

CREATE TRIGGER orders_stats
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION trg_orders_stats();

-- 6) Inspect / disable / drop
SELECT tgname, tgrelid::regclass, tgtype, tgenabled FROM pg_trigger WHERE NOT tgisinternal;
ALTER TABLE orders DISABLE TRIGGER orders_audit;     -- handy during bulk loads
ALTER TABLE orders ENABLE  TRIGGER orders_audit;
DROP TRIGGER IF EXISTS orders_audit ON orders;

-- 7) Performance + gotchas
-- - Every trigger runs inside the writer's transaction; heavy triggers slow writes.
-- - Avoid SELECTs against other large tables in triggers; do that work async.
-- - For cross-system effects (queues, HTTP) use an OUTBOX table + a worker, not a trigger.
-- - Statement-level triggers + transition tables (NEW TABLE, OLD TABLE) handle bulk DML efficiently.

-- 8) When a trigger is the WRONG tool
-- - Anything calling external services
-- - Anything that can be done in the application with the same atomicity guarantee
-- - Anything you cannot debug from EXPLAIN ANALYZE

CREATE OR REPLACE FUNCTION touch_updated()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_touch BEFORE UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION touch_updated();