PostgreSQL 触发器详解：自动化数据管理的利器

PostgreSQL 触发器（Trigger）是一种强大的数据库功能，能够在特定数据操作（如插入、更新、删除）发生时自动执行预定义的逻辑。通过触发器，我们从而可以实现数据校验、日志审计、级联操作等复杂业务逻辑，而无需依赖外部应用程序代码。

一、触发器概念

1. 什么是触发器？

首先了解一下什么是触发器，触发器是一种与数据库表事件绑定的自动化机制。当指定的事件（如 INSERT、UPDATE、DELETE）在表中发生时，触发器会自动执行关联的数据库函数（通常用 PL/pgSQL 编写），从而实现数据处理的自动化。

ps:对于event trigger不在此做讨论

2. 触发器的类型

• 行级触发器（Row-Level）
  每处理一行数据触发一次。例如，插入 1200 行数据会触发 1200 次。

• 语句级触发器（Statement-Level）
  每个 SQL 语句触发一次，无论影响多少行数据。例如，删除 10000 行数据仅触发一次。

• 触发时机

  • BEFORE：在操作执行前触发（可修改数据或阻止操作）。
  • AFTER：在操作执行后触发（适合日志记录或后续处理）。
  • INSTEAD OF：仅用于视图，替代原始操作执行自定义逻辑。

二、触发器的创建与使用

1. 创建触发函数

触发器的核心逻辑通过函数实现，函数需返回 TRIGGER 类型，并可通过 NEW 和 OLD 变量访问数据变更前后的值。

-- 示例：自动更新修改时间的函数
CREATE OR REPLACE FUNCTION update_orderamount_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.orderamount = NOW();  
    RETURN NEW;              
END;
$$ 
LANGUAGE "plpgsql";

2. 触发器关联

通过 CREATE TRIGGER 将函数绑定到function。

-- 在 order_detail 表的 UPDATE 操作前触发
CREATE TRIGGER trg_order_detail_update
BEFORE UPDATE ON order_detail
FOR EACH ROW  -- 行级触发器
EXECUTE FUNCTION update_orderamount_at();

• 关键参数说明：
  • BEFORE/AFTER：触发时机。
  • INSERT/UPDATE/DELETE：监听的操作类型。
  • FOR EACH ROW/STATEMENT：行级或语句级触发。

3. 访问数据变量

• NEW：在 INSERT 和 UPDATE 中表示新数据行。
• OLD：在 UPDATE 和 DELETE 中表示旧数据行。
• TG_OP：获取触发操作类型 DML（如 'INSERT','UPDATE','DELETE'）。

三、触发器的典型应用场景

1. 自动维护时间戳

在插入或更新记录时，自动填充 created_date 和 modified_date 字段。

CREATE OR REPLACE FUNCTION set_ordertimes()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        NEW.created_date = NOW();
        NEW.modified_date = NOW();
    ELSIF (TG_OP = 'UPDATE') THEN
        NEW.modified_date = NOW();
    END IF;
    RETURN NEW;
END;
$$ 
LANGUAGE "plpgsql";

-- 绑定到 order_detail 表,关联函数
CREATE TRIGGER trg_order_times
BEFORE INSERT OR UPDATE ON order_detail
FOR EACH ROW
EXECUTE FUNCTION set_ordertimes();

2. 数据审计与日志记录

记录所有对关键表的修改操作，用于追踪数据变更历史。

-- 创建审计表
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    action_time TIMESTAMPTZ DEFAULT NOW(),
    action VARCHAR(10),  -- INSERT/UPDATE/DELETE
    table_name TEXT,
    old_data JSONB,
    new_data JSONB
);

-- 审计触发器函数
CREATE OR REPLACE FUNCTION log_audit()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (action, table_name, old_data)
        VALUES ('DELETE', TG_TABLE_NAME, to_jsonb(OLD));
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (action, table_name, old_data, new_data)
        VALUES ('UPDATE', TG_TABLE_NAME, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (action, table_name, new_data)
        VALUES ('INSERT', TG_TABLE_NAME, to_jsonb(NEW));
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定到 orders 表
CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_audit();

3. 数据校验与业务规则

阻止涉及到的不符合条件的操作，例如禁止负库存,这个跟在表上加check约束功能类似

CREATE OR REPLACE FUNCTION prevent_negative_stock()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.quantity < 0 THEN
        RAISE EXCEPTION '库存数量不能为负数!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 在库存表上设置校验
CREATE TRIGGER trg_check_stock
BEFORE INSERT OR UPDATE ON inventory
FOR EACH ROW
EXECUTE FUNCTION prevent_negative_stock();

四、触发器的管理

1. 查看触发器

-- 查看表的所有触发器
SELECT tgname, tgtype, tgenabled 
FROM pg_trigger 
WHERE tgrelid = 'orders'::regclass;

-- 查看触发器定义的函数
SELECT pg_get_triggerdef(oid) 
FROM pg_trigger 
WHERE tgname = 'trg_order_times';

2. 启用/禁用触发器

-- 禁用触发器
ALTER TABLE users DISABLE TRIGGER trg_order_times;

-- 启用触发器
ALTER TABLE users ENABLE TRIGGER trg_order_times;

3. 删除触发器

DROP TRIGGER trg_order_times;

五、注意事项与最佳实践

1. 性能优化

   • 避免在触发器中执行复杂查询或频繁 I/O 操作。
   • 谨慎使用行级触发器处理高频写入的大表。

2. 避免递归触发
   确保触发器逻辑不会间接触发自身，例如在触发器内更新同一表。

3. 测试与调试

   • 使用 RAISE NOTICE 输出调试信息。
   • 在测试环境充分验证逻辑后再部署到生产环境。

4. 与其他机制结合

   • 优先使用 CHECK 约束实现简单校验。
   • 使用 RULE 重写查询逻辑（适用于特定场景）。