Triggers vs Application Logic

-- A trigger is a stored block of SQL the database runs
-- AUTOMATICALLY whenever a row is INSERTed, UPDATEd, or DELETEd.
-- You never call it — the database fires it for you.

-- In Postgres, a trigger has TWO parts:
--   1) a FUNCTION that holds the logic
--   2) a TRIGGER that binds that function to a table + event

CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER AS $$            -- $$ marks the function body
BEGIN
    NEW.updated_at = NOW();      -- NEW = the row as it will be 
...

-- The same idea on MySQL/MariaDB uses inline trigger bodies
-- (no separate function). Same concepts, different syntax:

CREATE TRIGGER trg_products_set_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW
    SET NEW.updated_at = NOW();   -- MySQL: logic lives in the trigger itself

-- TIMING + EVENT decide WHEN a trigger runs:
--   BEFORE INSERT/UPDATE  → can read AND change NEW before it is saved
--   AFTER  INSERT/UPDATE  → row is already saved; NEW/OLD are read-only
--   AFTER  DELETE
...

-- ✅ GREAT use of a trigger: an audit log you CANNOT bypass.
-- Even a buggy app or a hand-typed UPDATE gets recorded.

CREATE TABLE audit_log (
    id          SERIAL PRIMARY KEY,
    table_name  TEXT NOT NULL,
    operation   TEXT NOT NULL,          -- INSERT / UPDATE / DELETE
    old_data    JSONB,                  -- the row before (NULL for INSERT)
    new_data    JSONB,                  -- the row after  (NULL for DELETE)
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMP
...

-- ✅ GREAT use of a trigger: keep a DENORMALISED count in sync.
-- categories.product_count is a cached number we maintain on writes,
-- so reads never have to COUNT the products table.

CREATE OR REPLACE FUNCTION sync_product_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE categories SET product_count = product_count + 1
        WHERE id = NEW.category_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE categories SET product_count = product_count - 1
        WHER
...

-- ❌ When NOT to use a trigger: hidden side-effects.
-- Triggers run INSIDE your transaction. Anything slow or external
-- (email, HTTP call, payment API) makes the whole write hang or
-- roll back in surprising ways.

-- ❌ BAD: emailing from a trigger
-- CREATE TRIGGER send_welcome_email AFTER INSERT ON users ...
--   If the mail server is down, the INSERT itself fails!

-- ✅ GOOD: do the write, then handle the side-effect in the app
--   BEGIN;
--     INSERT INTO users (email) VALUES ('sam@x.c
...

-- The four pitfalls that make people fear triggers.

-- PITFALL 1 — Cascading / recursive triggers.
-- A trigger on orders that UPDATEs orders fires itself again.
-- Fix: guard on what actually changed, IS DISTINCT FROM is your friend.
CREATE OR REPLACE FUNCTION on_status_change()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN   -- only when it really changed
        INSERT INTO order_history (order_id, status) VALUES (NEW.order_id, NEW.status);
    END IF;
    R
...