触发器与事件

触发器让数据库在特定事件（INSERT、UPDATE、DELETE）发生时自动执行函数，是维护数据完整性和实现自动化逻辑的强大工具。

触发器基础

创建触发器的流程

-- 1. 先创建一个触发器函数（返回 TRIGGER 类型）
CREATE OR REPLACE FUNCTION trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 逻辑代码
    RETURN NEW;  -- 或 RETURN OLD，或 RETURN NULL
END;
$$;

-- 2. 将函数绑定到表上
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE | TRUNCATE}
ON table_name
[FOR EACH {ROW | STATEMENT}]
EXECUTE FUNCTION trigger_function();

新旧数据的含义

场景	OLD	NEW
INSERT	NULL	新插入的行
UPDATE	更新前的行	更新后的行
DELETE	被删除的行	NULL

实战：自动更新时间戳

这是最常用的触发器——自动维护 updated_at 列：

-- 创建通用更新时间函数
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

-- 应用到表
CREATE TRIGGER trg_products_timestamp
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

-- 测试
UPDATE products SET stock = stock + 10 WHERE id = 1;
SELECT id, name, stock, updated_at FROM products;

审计日志触发器

-- 创建审计日志表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT DEFAULT CURRENT_USER,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建审计触发器函数
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, TG_OP, TO_JSONB(NEW));
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- 只记录有变化的字段
        IF OLD IS DISTINCT FROM NEW THEN  -- 判断是否有变化
            INSERT INTO audit_log (table_name, operation, old_data, new_data)
            VALUES (TG_TABLE_NAME, TG_OP, 
                    TO_JSONB(OLD), TO_JSONB(NEW));
        END IF;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, TG_OP, TO_JSONB(OLD));
    END IF;
    
    RETURN NEW;
END;
$$;

-- 绑定到 products 表
CREATE TRIGGER trg_products_audit
    AFTER INSERT OR UPDATE OR DELETE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_func();

-- 测试
INSERT INTO products (name, category, price, stock) 
VALUES ('测试商品', '测试', 100, 50);

UPDATE products SET price = 80 WHERE name = '测试商品';

DELETE FROM products WHERE name = '测试商品';

-- 查看审计日志
SELECT * FROM audit_log ORDER BY id;

约束触发器

用触发器实现复杂约束（CHECK 约束无法做到的）：

CREATE OR REPLACE FUNCTION check_order_limit()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    monthly_total NUMERIC;
BEGIN
    -- 每个客户每月订单总额不超过 100000
    SELECT COALESCE(SUM(total_amount), 0)
    INTO monthly_total
    FROM orders
    WHERE customer_id = NEW.customer_id
        AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', NOW())
        AND status != '已取消';
    
    IF monthly_total + NEW.total_amount > 100000 THEN
        RAISE EXCEPTION '客户 % 本月累计已超 %，拒绝下单', 
              NEW.customer_id, monthly_total || ' + ' || NEW.total_amount || ' = ' || (monthly_total + NEW.total_amount);
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_limit
    BEFORE INSERT ON orders
    FOR EACH ROW
    EXECUTE FUNCTION check_order_limit();

级联更新触发器

不用外键 ON UPDATE CASCADE，而是用触发器控制更复杂的级联逻辑：

-- 保存订单总金额到客户表
CREATE OR REPLACE FUNCTION update_customer_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        UPDATE customers 
        SET total_orders = (
            SELECT COALESCE(SUM(total_amount), 0) 
            FROM orders 
            WHERE customer_id = NEW.customer_id AND status = '已完成'
        )
        WHERE customer_id = NEW.customer_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE customers
        SET total_orders = (
            SELECT COALESCE(SUM(total_amount), 0)
            FROM orders
            WHERE customer_id = OLD.customer_id AND status = '已完成'
        )
        WHERE customer_id = OLD.customer_id;
    END IF;
    
    RETURN NEW;
END;
$$;

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

事件触发器（DDL 触发器）

除了 DML（INSERT/UPDATE/DELETE）触发器，PostgreSQL 还支持事件触发器，在 DDL 事件（CREATE TABLE、DROP TABLE 等）时触发：

-- 禁止删除表（除非是超级用户）
CREATE OR REPLACE FUNCTION prevent_table_drop()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION '不允许删除表';
END;
$$;

CREATE EVENT TRIGGER evttr_prevent_drop
    ON SQL_DROP
    EXECUTE FUNCTION prevent_table_drop();

-- 记录所有 DDL 变更
CREATE TABLE ddl_audit (
    id SERIAL PRIMARY KEY,
    event_type TEXT,
    object_type TEXT,
    object_identity TEXT,
    command_tag TEXT,
    username TEXT DEFAULT CURRENT_USER(),
    occurred_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_ddl_events()
RETURNS EVENT_TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO ddl_audit (event_type, object_type, object_identity, command_tag)
    VALUES (
        TG_EVENT,
        TG_OBJECT_TYPE,
        TG_OBJECT_IDENTITY,
        TG_TAG
    );
    RETURN NEW;
END;
$$;

CREATE EVENT TRIGGER log_ddl
    ON ddl_command_end
    EXECUTE FUNCTION log_ddl_events();

-- 测试
CREATE TABLE temp_test (id INT);
DROP TABLE temp_test;
SELECT * FROM ddl_audit;

条件触发

-- 只在特定列更新时触发
CREATE TRIGGER trg_price_change
    BEFORE UPDATE OF price ON products
    FOR EACH ROW
    WHEN (OLD.price IS DISTINCT FROM NEW.price)
    EXECUTE FUNCTION price_change_trigger();

-- 只在特定条件下触发
CREATE TRIGGER trg_low_stock
    AFTER UPDATE ON products
    FOR EACH ROW
    WHEN (NEW.stock < 10 AND OLD.stock >= 10)
    EXECUTE FUNCTION low_stock_alert();

触发器顺序控制

多个触发器可以作用于同一事件，PostgreSQL 按字母序执行。可以通过设置优先级字段控制：

CREATE TRIGGER trg_products_a_timestamp  -- 先执行（字母序靠前）
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER trg_products_z_audit  -- 后执行（字母序靠后）
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger_func();

性能影响

FOR EACH ROW vs FOR EACH STATEMENT：行级触发器每条受影响行都执行，表级触发器一个 SQL 只执行一次。批量操作时差异巨大
BEFORE vs AFTER：BEFORE 触发器可以修改数据，AFTER 触发器只能"观察"不修改

-- FOR EACH STATEMENT 示例：批量更新后只记录一次
CREATE OR REPLACE FUNCTION log_batch_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, new_data)
    VALUES (TG_TABLE_NAME, TG_OP, 
            TO_JSONB(format('批量更新了 %s 条数据', TG_ROW_COUNT)));
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_batch_audit
    AFTER UPDATE ON orders
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_batch_update();

常见陷阱

1. 触发器递归

-- 如果触发器 A 更新了表 T，表 T 的更新又触发了触发器 A
-- 可能导致无限递归！
-- 默认限制是 100 层递归
SET session_max_stack_depth = 2048;  -- 默认 2048KB

-- 用 pg_trigger_depth() 防止递归
CREATE OR REPLACE FUNCTION safe_update_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;
    
    -- 业务逻辑
    RETURN NEW;
END;
$$;

2. 隐性性能问题

-- 看似简单的 INSERT，可能触发多个触发器
-- INSERT -> BEFORE INSERT 触发器 -> BEFORE UPDATE 触发器
--           AFTER INSERT 触发器 -> AFTER UPDATE 触发器
-- 每个触发器都要执行函数，累加起来不可忽视

3. 触发器无法被事务隔离

-- 事务内的 INSERT/UPDATE 会立刻触发 BEFORE 触发器
-- 即使事务最终 ROLLBACK，BEFORE 触发器的副作用可能已生效
BEGIN;
    INSERT INTO products (...) VALUES (...);
    -- BEFORE 触发器已经执行了
ROLLBACK;

小结

触发器是数据库自动化的核心工具。在审计日志、复杂约束、级联更新、自动时间戳等场景下，触发器能减少应用层代码，确保数据逻辑在数据库层面的一致性。但触发器也有隐形成本——它增加了操作的复杂性、调试难度和性能开销。使用前问自己：“这个逻辑真的必须在数据库层吗？”

下一篇文章将学习 PostgreSQL 的备份与恢复策略。

Summary: INSERT/UPDATE/DELETE 触发器、事件触发器、审计日志与注意事项。