觸發器與事件觸發器：PostgreSQL 的自動化執行機制 | PostgreSQL

CREATE OR REPLACE FUNCTION my_trigger_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- BEFORE ROW: 回傳 NEW 讓操作繼續，回傳 NULL 取消操作
    -- AFTER ROW / STATEMENT: 回傳值被忽略，慣例回傳 NULL
    RETURN NEW;
END;
$$;

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER 觸發器名稱
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE [ OF 欄位 [, ...] ] | DELETE | TRUNCATE } [ OR ... ]
ON 資料表名稱
[ REFERENCING { { OLD | NEW } TABLE [ AS ] 別名 } [...] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( 條件 ) ]
EXECUTE FUNCTION 函式名稱 ( 引數 );

-- 移除
DROP TRIGGER 觸發器名稱 ON 資料表名稱;

-- 暫時停用/啟用
ALTER TABLE 資料表名稱 DISABLE TRIGGER 觸發器名稱;
ALTER TABLE 資料表名稱 ENABLE TRIGGER 觸發器名稱;
ALTER TABLE 資料表名稱 DISABLE TRIGGER ALL;

CREATE OR REPLACE FUNCTION set_timestamps()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.created_at = CURRENT_TIMESTAMP;
        NEW.updated_at = CURRENT_TIMESTAMP;
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.created_at = OLD.created_at;  -- 防止覆寫
        NEW.updated_at = CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;  -- 回傳修改後的列
END;
$$;

CREATE TRIGGER trg_set_timestamps
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_timestamps();

CREATE OR REPLACE FUNCTION validate_product_price()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.price < 0 THEN
        RAISE EXCEPTION '價格不得為負數：%', NEW.price;
    END IF;

    IF NEW.discount_price IS NOT NULL AND NEW.discount_price >= NEW.price THEN
        RAISE EXCEPTION '折扣價（%）必須低於原價（%）',
            NEW.discount_price, NEW.price;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION validate_product_price();

-- 建立稽核日誌表
CREATE TABLE audit_log (
    id          bigserial PRIMARY KEY,
    table_name  text        NOT NULL,
    operation   text        NOT NULL,
    old_data    jsonb,
    new_data    jsonb,
    changed_by  text        NOT NULL DEFAULT current_user,
    changed_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 通用稽核函式（可套用至任何表）
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER  -- 以函式擁有者身份執行
AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_data, new_data)
    VALUES (
        TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE NULL END
    );
    RETURN NULL;  -- AFTER Trigger 回傳值被忽略
END;
$$;

-- 套用至任意資料表
CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_func();

-- 只在 status 真正改變時才記錄
CREATE TRIGGER trg_status_change
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_status_change();

-- 只在金額超過門檻時觸發審核
CREATE TRIGGER trg_high_value_order
AFTER INSERT ON orders
FOR EACH ROW
WHEN (NEW.total_amount > 100000)
EXECUTE FUNCTION notify_finance_team();

-- 只在 price 或 stock_quantity 更新時才重新計算
CREATE TRIGGER trg_recalculate_cache
AFTER UPDATE OF price, stock_quantity ON products
FOR EACH ROW
EXECUTE FUNCTION recalculate_product_cache();

-- 組合 UPDATE OF 與 WHEN
CREATE TRIGGER trg_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION log_price_change();

-- 跨表視圖
CREATE VIEW order_summary AS
SELECT
    o.id AS order_id, o.total_amount,
    c.name AS customer_name, c.email AS customer_email
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- INSTEAD OF Trigger 讓視圖可 UPDATE
CREATE OR REPLACE FUNCTION update_order_summary()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders SET total_amount = NEW.total_amount
    WHERE id = NEW.order_id;

    UPDATE customers
    SET name = NEW.customer_name, email = NEW.customer_email
    WHERE id = (SELECT customer_id FROM orders WHERE id = NEW.order_id);

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_update_order_summary
INSTEAD OF UPDATE ON order_summary
FOR EACH ROW
EXECUTE FUNCTION update_order_summary();

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
        INSERT INTO ddl_audit_log (
            event_type, object_type, object_identity,
            command_tag, executed_by, executed_at
        ) VALUES (
            TG_EVENT, r.object_type, r.object_identity,
            r.command_tag, current_user, CURRENT_TIMESTAMP
        );
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_audit_ddl
ON ddl_command_end
EXECUTE FUNCTION log_ddl_event();

CREATE OR REPLACE FUNCTION prevent_table_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP
        IF r.object_type = 'table' AND r.schema_name = 'public' THEN
            RAISE EXCEPTION '禁止在 public schema 刪除資料表：%', r.object_name;
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_prevent_drop
ON sql_drop
EXECUTE FUNCTION prevent_table_drop();

CREATE OR REPLACE FUNCTION soft_delete()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE documents SET deleted_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
    RETURN NULL;  -- 取消實際 DELETE
END;
$$;

CREATE TRIGGER trg_soft_delete
BEFORE DELETE ON documents
FOR EACH ROW
WHEN (OLD.deleted_at IS NULL)
EXECUTE FUNCTION soft_delete();

-- 搭配視圖隱藏已刪除的列
CREATE VIEW active_documents AS
SELECT * FROM documents WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION log_field_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    old_data jsonb; new_data jsonb;
    diff jsonb := '{}'; key text;
BEGIN
    old_data := to_jsonb(OLD);
    new_data := to_jsonb(NEW);

    FOR key IN SELECT jsonb_object_keys(new_data) LOOP
        IF old_data->key IS DISTINCT FROM new_data->key THEN
            diff := diff || jsonb_build_object(
                key, jsonb_build_object('from', old_data->key, 'to', new_data->key)
            );
        END IF;
    END LOOP;

    IF diff <> '{}' THEN
        INSERT INTO field_change_log (table_name, row_id, changed_fields)
        VALUES (TG_TABLE_NAME, NEW.id, diff);
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_field_changes
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_field_changes();

CREATE OR REPLACE FUNCTION sync_order_total()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(unit_price * quantity), 0)
        FROM order_items
        WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE id = COALESCE(NEW.order_id, OLD.order_id);
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_sync_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW
EXECUTE FUNCTION sync_order_total();

CREATE OR REPLACE FUNCTION audit_bulk_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO bulk_audit_log (table_name, operation, affected_count, sample_ids)
    SELECT
        TG_TABLE_NAME, TG_OP, COUNT(*),
        array_agg(id ORDER BY id) FILTER (WHERE id IS NOT NULL)
    FROM new_data;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_bulk_audit
AFTER UPDATE ON orders
REFERENCING OLD TABLE AS old_data NEW TABLE AS new_data
FOR EACH STATEMENT
EXECUTE FUNCTION audit_bulk_update();

-- Constraint Trigger 可延遲到交易結束時驗證
CREATE CONSTRAINT TRIGGER trg_check_balance
AFTER INSERT OR UPDATE ON account_transfers
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION check_account_balance();

BEGIN;
SET CONSTRAINTS trg_check_balance IMMEDIATE;  -- 臨時改為立即驗證
COMMIT;

-- 查詢 Trigger 函式的效能統計
SELECT
    funcname, calls, total_time, self_time,
    round((total_time / calls)::numeric, 3) AS avg_ms
FROM pg_stat_user_functions
WHERE funcname LIKE '%trigger%'
ORDER BY total_time DESC;

-- 使用 pg_trigger_depth() 偵測遞迴深度
CREATE OR REPLACE FUNCTION safe_update_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;  -- 已在 Trigger 內，跳過
    END IF;
    -- 正常邏輯...
    RETURN NEW;
END;
$$;