Creating and Managing Triggers

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

CREATE TRIGGER set_updated
BEFORE UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE TRIGGER log_orders
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION log_new_order();

CREATE TRIGGER v_users_ins
INSTEAD OF INSERT ON v_users
FOR EACH ROW EXECUTE FUNCTION users_ins_handler();

CREATE TRIGGER trg FOR EACH ROW ...   -- fires per row

CREATE TRIGGER orders_changed
AFTER INSERT OR UPDATE OR DELETE ON orders
REFERENCING NEW TABLE AS new_rows OLD TABLE AS old_rows
FOR EACH STATEMENT EXECUTE FUNCTION fan_out();

CREATE FUNCTION touch_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $
BEGIN NEW.updated_at := now(); RETURN NEW; END $;

IF OLD.status <> NEW.status THEN
   INSERT INTO status_log VALUES(NEW.id, OLD.status, NEW.status, now());
END IF;

IF TG_OP = 'DELETE' THEN ... RETURN OLD;
ELSIF TG_OP = 'INSERT' THEN ... RETURN NEW;
ELSE ... RETURN NEW;
END IF;

RAISE NOTICE 'fired on %.%', TG_TABLE_SCHEMA, TG_TABLE_NAME;

CREATE TRIGGER paid_only
AFTER UPDATE OF status ON orders
FOR EACH ROW WHEN (OLD.status <> 'paid' AND NEW.status = 'paid')
EXECUTE FUNCTION on_payment();

SELECT tgname, tgrelid::regclass, tgtype, tgenabled
  FROM pg_trigger WHERE NOT tgisinternal;
\d+ orders                       -- shows triggers