【PostgreSQL】触发器使用教程与实践

引言

在数据库应用开发中，我们经常会遇到这样的需求：插入数据时自动校验格式、修改数据时自动记录日志、删除数据时自动更新汇总表……这些操作如果全部放在应用层处理，不仅会增加代码复杂度，还容易因为遗漏导致数据不一致。

PostgreSQL 的触发器正是解决这些问题的利器。它就像数据库里的“自动服务员”——当你对表执行插入、更新、删除等操作时，会自动触发预设的逻辑，帮你完成那些“想自动执行”的任务。

本文核心内容：

触发器的核心概念与工作原理
触发器的完整语法与创建流程
5 个实战案例覆盖高频场景
使用注意事项与避坑指南

一、触发器核心概念

1.1 触发器是什么？

触发器 = 触发条件 + 触发函数

触发条件：规定“在什么时间、对什么表、做什么操作时触发”
触发函数：触发后要执行的具体逻辑（用 PL/pgSQL 编写）

1.2 三个核心维度

维度	选项	说明
触发时机	`BEFORE`	操作前触发，可用于数据校验或修改
	`AFTER`	操作后触发，可用于日志记录、汇总更新
	`INSTEAD OF`	替代操作，仅用于视图
触发级别	`FOR EACH ROW`	每修改一行触发一次
	`FOR EACH STATEMENT`	整个 SQL 只触发一次
触发操作	`INSERT` / `UPDATE` / `DELETE` / `TRUNCATE`	可组合使用

1.3 关键特殊变量

在触发器函数中，可以访问以下特殊变量：

变量	说明
`NEW`	插入/更新后的新行数据
`OLD`	更新/删除前的旧行数据
`TG_OP`	当前触发的操作类型（INSERT/UPDATE/DELETE）
`TG_NAME`	触发器名称
`TG_WHEN`	触发时机（BEFORE/AFTER）
`TG_LEVEL`	触发级别（ROW/STATEMENT）

二、创建触发器

触发器的创建分两步：

创建触发器函数：编写要执行的逻辑
创建触发器：绑定触发条件与函数

2.1 语法结构

步骤 1：创建触发器函数（通用模板）

CREATE OR REPLACE FUNCTION 函数名() 
RETURNS TRIGGER AS $$
BEGIN
    -- 业务逻辑
    RETURN NEW;  -- 或 RETURN NULL / RETURN OLD
END;
$$ LANGUAGE plpgsql;

步骤 2：创建触发器

CREATE TRIGGER 触发器名
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | TRUNCATE}
ON 表名
[FOR EACH {ROW | STATEMENT}]
[WHEN (条件)]
EXECUTE FUNCTION 触发器函数名();

三、实战案例

案例 1：数据校验 + 自动填充

需求：员工表插入/更新时，自动校验数据合法性并填充时间字段。

-- 1. 创建表
CREATE TABLE emp (
    empname   text,
    salary    integer,
    last_date timestamp,
    last_user text
);

-- 2. 创建触发器函数
CREATE OR REPLACE FUNCTION emp_check_and_fill() 
RETURNS TRIGGER AS $$
BEGIN
    -- 校验：姓名不能为空
    IF NEW.empname IS NULL OR NEW.empname = '' THEN
        RAISE EXCEPTION '员工姓名不能为空';
    END IF;
    
    -- 校验：薪资不能为负
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION '薪资不能为负数';
    END IF;
    
    -- 自动填充：更新时间与操作人
    NEW.last_date = CURRENT_TIMESTAMP;
    NEW.last_user = CURRENT_USER;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. 创建触发器
CREATE TRIGGER emp_trigger
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
EXECUTE FUNCTION emp_check_and_fill();

案例 2：自动更新时间戳

需求：任何更新操作都自动记录更新时间。

-- 1. 创建表
CREATE TABLE employees (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(50) NOT NULL,
    salary      NUMERIC(10,2) NOT NULL,
    last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. 创建触发器函数
CREATE OR REPLACE FUNCTION update_last_update() 
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_update = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 3. 创建触发器
CREATE TRIGGER trigger_update_last_update
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_update();

案例 3：操作日志记录

需求：记录所有对员工表的增删改操作。

-- 1. 创建操作日志表
CREATE TABLE emp_audit (
    id          SERIAL PRIMARY KEY,
    action      VARCHAR(10),      -- INSERT/UPDATE/DELETE
    empname     text,
    salary      integer,
    change_time timestamp DEFAULT CURRENT_TIMESTAMP,
    changed_by  text
);

-- 2. 创建触发器函数
CREATE OR REPLACE FUNCTION emp_audit_log() 
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO emp_audit (action, empname, salary, changed_by)
        VALUES ('INSERT', NEW.empname, NEW.salary, CURRENT_USER);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO emp_audit (action, empname, salary, changed_by)
        VALUES ('UPDATE', NEW.empname, NEW.salary, CURRENT_USER);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO emp_audit (action, empname, salary, changed_by)
        VALUES ('DELETE', OLD.empname, OLD.salary, CURRENT_USER);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 3. 创建触发器（同时覆盖三种操作）
CREATE TRIGGER emp_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
EXECUTE FUNCTION emp_audit_log();

案例 4：条件触发（WHEN 子句）

需求：只有当订单状态从“待处理”变为“已完成”时，才更新客户统计表。

-- 1. 创建订单表
CREATE TABLE orders (
    order_id     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  INT NOT NULL,
    total_amount NUMERIC NOT NULL DEFAULT 0,
    status       VARCHAR(20) NOT NULL
);

-- 2. 创建客户统计表
CREATE TABLE customer_stats (
    customer_id  INT PRIMARY KEY,
    total_spent  NUMERIC NOT NULL DEFAULT 0
);

-- 3. 创建触发器函数
CREATE OR REPLACE FUNCTION update_customer_stats() 
RETURNS TRIGGER AS $$
BEGIN
    UPDATE customer_stats 
    SET total_spent = total_spent + NEW.total_amount
    WHERE customer_id = NEW.customer_id;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 4. 创建触发器（带条件）
CREATE TRIGGER update_customer_stats_trigger
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status <> 'completed' AND NEW.status = 'completed')
EXECUTE FUNCTION update_customer_stats();

案例 5：禁止删除特定数据

需求：不允许删除工资超过 5000 的员工记录。

CREATE OR REPLACE FUNCTION prevent_high_salary_delete() 
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary > 5000 THEN
        RAISE EXCEPTION '不能删除工资超过5000的员工: %', OLD.empname;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_delete_trigger
BEFORE DELETE ON emp
FOR EACH ROW
EXECUTE FUNCTION prevent_high_salary_delete();

案例 6：批量创建更新时间戳触发器

需求：在多张表中统一实现 updated_at 字段的自动更新，避免重复编写相同的触发器。

-- 1. 创建通用的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 批量创建触发器（使用 DO 块动态执行）
DO $$
DECLARE
    t TEXT;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'users',
        'exams',
        'exam_submissions',
        'exam_reviews',
        'resume_reviews',
        'interview_sessions',
        'interview_questions',
        'qa_sessions'
    ]
    LOOP
        EXECUTE format('
            CREATE TRIGGER trg_%s_updated_at
            BEFORE UPDATE ON %s
            FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
        ', t, t);
    END LOOP;
END;
$$;

四、管理与维护

4.1 查看触发器

-- 查看所有触发器
SELECT * FROM pg_trigger;

-- 查看特定表的触发器
SELECT tgname, tgrelid::regclass AS table_name 
FROM pg_trigger 
WHERE tgrelid = 'emp'::regclass;

-- 查看触发器函数
SELECT proname, prosrc 
FROM pg_proc 
WHERE proname = 'emp_check_and_fill';

4.2 删除触发器

DROP TRIGGER trigger_name ON table_name;

-- 示例
DROP TRIGGER emp_trigger ON emp;

4.3 启用/禁用触发器

-- 禁用触发器
ALTER TABLE emp DISABLE TRIGGER emp_trigger;

-- 启用触发器
ALTER TABLE emp ENABLE TRIGGER emp_trigger;

-- 禁用所有触发器
ALTER TABLE emp DISABLE TRIGGER ALL;

五、核心规则与注意事项

5.1 返回值规则

触发器类型	返回值	效果
`BEFORE INSERT/UPDATE`	`RETURN NEW;`	正常执行，NEW 为实际插入/更新的行
`BEFORE INSERT/UPDATE`	`RETURN NULL;`	跳过本次操作，不插入/更新该行
`BEFORE DELETE`	`RETURN OLD;`	正常执行删除
`BEFORE DELETE`	`RETURN NULL;`	跳过本次删除
`AFTER` 触发器	`RETURN NULL;`	返回值被忽略，建议返回 NULL

5.2 重要注意事项

触发器函数必须返回 trigger 类型：函数声明为 RETURNS TRIGGER。
BEFORE 触发器可以修改数据：通过修改 NEW 行，可以影响最终插入/更新的数据。
AFTER 触发器中的 NEW/OLD 不可修改：修改不会生效。
语句级触发器无法访问 NEW/OLD：FOR EACH STATEMENT 级别的触发器不能访问行数据。
触发器执行顺序：同一事件有多个触发器时，按名称字母顺序执行。
事务回滚：触发器与触发语句在同一个事务中，任何一方失败都会回滚全部操作。

六、触发器的适用场景

场景	说明	推荐级别
数据校验	确保数据符合业务规则	BEFORE ROW
自动填充	时间戳、操作用户等	BEFORE ROW
操作日志	审计追踪	AFTER ROW
汇总更新	更新统计表、缓存表	AFTER ROW/STATEMENT
约束实现	复杂的跨表约束	约束触发器
视图更新	让视图支持增删改	INSTEAD OF

七、总结

PostgreSQL 触发器是数据库自动化运维的核心工具：

创建流程：触发器函数 → 触发器（两步走）
核心变量：NEW、OLD、TG_OP
关键选择：BEFORE/AFTER、ROW/STATEMENT
返回值决定行为：NULL 跳过操作，NEW/OLD 继续执行

扩展阅读：

PostgreSQL 官方文档 - 触发器

PL/pgSQL 触发器函数

posted @ 2026-08-15 17:01 静心笃行。阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

静心笃行。

【PostgreSQL】触发器 使用教程与实践

引言