PL/pgSQL 入门教程（五）：触发器-阿里云开发者社区

你是否想让数据库自动完成这些操作：插入数据时自动校验格式、修改数据时自动记录日志、删除数据时自动更新汇总表？PostgreSQL的触发器（Trigger）就是干这个的——它像数据库里的“自动服务员”，当你对表执行插入、更新、删除等操作时，会自动触发预设的函数，完成你想要的逻辑。

一、触发器核心概念（5分钟）

在写代码前，先搞懂3个核心问题：触发器是什么、什么时候触发、触发后做什么。

1. 触发器的本质

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

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

2. 关键术语（通俗解释）

术语	通俗说明
触发时机	BEFORE（操作前触发）：比如插入前校验数据； AFTER（操作后触发）：比如插入后记录日志； INSTEAD OF（替代操作）：仅用于视图，让不可更新的视图能修改
触发级别	ROW（行级）：每修改1行触发1次（比如更新10行触发10次）； STATEMENT（语句级）：不管改多少行，整个SQL只触发1次
触发操作	INSERT/UPDATE/DELETE/TRUNCATE（对表的操作类型）
特殊变量（核心）	NEW：插入/更新后的新行数据； OLD：更新/删除前的旧行数据； TG_OP：触发的操作类型（INSERT/UPDATE/DELETE）

3. 核心规则

触发器函数必须返回 trigger（数据变更触发器）或 event_trigger（事件触发器）；
行级触发器的返回值会影响操作：BEFORE触发器返回NULL会跳过当前行，AFTER触发器返回值无意义（建议返回NULL）。

二、触发器基础语法（5分钟）

触发器的创建分两步：先写「触发函数」（逻辑核心），再创建「触发器」（绑定条件）。

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

CREATE OR REPLACE FUNCTION 函数名() RETURNS trigger AS {mathJaxContainer[0]} LANGUAGE plpgsql; -- 固定用plpgsql语言

步骤2：创建触发器（绑定函数与触发条件）

CREATE TRIGGER 触发器名
触发时机 BEFORE/AFTER/INSTEAD OF -- 比如BEFORE
触发操作 INSERT/UPDATE/DELETE -- 比如INSERT OR UPDATE
ON 表名/视图名 -- 要监控的表/视图
FOR EACH ROW/STATEMENT -- 触发级别，比如ROW
EXECUTE FUNCTION 触发器函数名(); -- 绑定第一步的函数

三、实战案例（15分钟）

下面5个案例覆盖触发器最常用的场景，每个案例都有完整代码和测试步骤，你可以直接复制到PostgreSQL中运行。

案例1：基础场景——数据校验+自动填充

需求：员工表（emp）插入/更新时，自动完成3件事：

校验员工姓名非空；
校验薪资非负；
自动记录最后修改时间和修改人。

步骤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 {mathJaxContainer[1]} LANGUAGE plpgsql;

步骤3：创建触发器

CREATE TRIGGER emp_trigger
BEFORE INSERT OR UPDATE ON emp -- 插入/更新前触发
FOR EACH ROW -- 行级触发，每改一行执行一次
EXECUTE FUNCTION emp_check_and_fill();

步骤4：测试效果

-- 测试1：插入合法数据（成功）
INSERT INTO emp (empname, salary) VALUES ('张三', 5000);

-- 测试2：插入空姓名（失败，触发校验）
INSERT INTO emp (empname, salary) VALUES (NULL, 5000);

-- 测试3：插入负薪资（失败，触发校验）
INSERT INTO emp (empname, salary) VALUES ('李四', -1000);

-- 测试4：更新数据，查看自动填充的字段
UPDATE emp SET salary = 6000 WHERE empname = '张三';
SELECT * FROM emp; -- 能看到last_date和last_user自动填充

案例2：高频场景——数据审计（记录所有变更）

需求：记录员工表的所有插入/更新/删除操作，保存到审计表（emp_audit），包含：操作类型、操作时间、操作用户、变更的数据。

版本1：行级触发器（适合少量数据）

步骤1：创建审计表

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL, -- 操作类型：I(插入)/U(更新)/D(删除)
    stamp             timestamp NOT NULL, -- 操作时间
    userid            text      NOT NULL, -- 操作用户
    empname           text      NOT NULL, -- 员工姓名
    salary            integer             -- 薪资
);

步骤2：编写审计函数

CREATE OR REPLACE FUNCTION emp_audit_func() RETURNS trigger AS {mathJaxContainer[2]} LANGUAGE plpgsql;

步骤3：创建审计触发器

CREATE TRIGGER emp_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON emp -- 操作后触发
FOR EACH ROW -- 行级触发
EXECUTE FUNCTION emp_audit_func();

步骤4：测试审计效果

-- 插入数据
INSERT INTO emp (empname, salary) VALUES ('王五', 8000);
-- 更新数据
UPDATE emp SET salary = 8500 WHERE empname = '王五';
-- 删除数据
DELETE FROM emp WHERE empname = '王五';

-- 查看审计记录
SELECT * FROM emp_audit;

版本2：语句级+过渡表（适合批量数据，性能更好）

如果一次修改10万行数据，行级触发器会触发10万次，性能很差。此时用「语句级触发器+过渡表」，整个SQL只触发1次，批量写入审计数据。

步骤1：重新创建审计函数（适配过渡表）

CREATE OR REPLACE FUNCTION emp_audit_batch_func() RETURNS trigger AS {mathJaxContainer[3]} LANGUAGE plpgsql;

步骤2：创建3个语句级触发器（分别绑定插入/更新/删除）

-- 插入触发器：关联新数据过渡表new_table
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT -- 语句级触发
EXECUTE FUNCTION emp_audit_batch_func();

-- 更新触发器：关联旧/新数据过渡表
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION emp_audit_batch_func();

-- 删除触发器：关联旧数据过渡表old_table
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION emp_audit_batch_func();

步骤3：测试批量审计

-- 批量插入10条数据
INSERT INTO emp (empname, salary) 
VALUES ('赵六', 7000), ('钱七', 7500), ('孙八', 9000);

-- 查看审计记录（只触发1次，批量写入）
SELECT * FROM emp_audit;

案例3：进阶场景——视图更新（INSTEAD OF触发器）

PostgreSQL的普通视图默认不能插入/更新/删除，用「INSTEAD OF触发器」可以让视图变成“可更新”，同时保留审计逻辑。

步骤1：重建主表+审计表

CREATE TABLE emp (
    empname           text PRIMARY KEY, -- 主键
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

步骤2：创建视图（展示员工+最后修改时间）

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated -- 最后修改时间
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

步骤3：编写视图触发器函数

CREATE OR REPLACE FUNCTION emp_view_update_func() RETURNS trigger AS {mathJaxContainer[4]} LANGUAGE plpgsql;

步骤4：创建INSTEAD OF触发器

CREATE TRIGGER emp_view_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view -- 替代原操作
FOR EACH ROW
EXECUTE FUNCTION emp_view_update_func();

步骤5：测试视图更新

-- 向视图插入数据（实际插入主表）
INSERT INTO emp_view (empname, salary) VALUES ('周九', 10000);

-- 更新视图数据
UPDATE emp_view SET salary = 11000 WHERE empname = '周九';

-- 删除视图数据
DELETE FROM emp_view WHERE empname = '周九';

-- 查看视图和审计表
SELECT * FROM emp_view;
SELECT * FROM emp_audit;

案例4：数据仓库场景——汇总表自动维护

需求：销售事实表（sales_fact）变更时，自动更新按时间维度的汇总表（sales_summary_bytime），避免每次查询都全表统计，提升查询效率。

步骤1：创建基础表

-- 时间维度表
CREATE TABLE time_dimension (
    time_key        integer NOT NULL, -- 时间主键（比如20240101）
    day_of_week     integer NOT NULL, -- 星期几
    day_of_month    integer NOT NULL, -- 几号
    month           integer NOT NULL, -- 月份
    year            integer NOT NULL  -- 年份
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

-- 销售事实表（核心业务表）
CREATE TABLE sales_fact (
    time_key        integer NOT NULL, -- 关联时间维度表
    product_key     integer NOT NULL, -- 产品主键
    amount_sold     numeric(12,2) NOT NULL, -- 销售额
    units_sold      integer NOT NULL -- 销售量
);

-- 时间维度汇总表（用于快速查询）
CREATE TABLE sales_summary_bytime (
    time_key        integer NOT NULL,
    amount_sold     numeric(15,2) NOT NULL, -- 累计销售额
    units_sold      numeric(12) NOT NULL    -- 累计销售量
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

步骤2：编写汇总表维护函数

CREATE OR REPLACE FUNCTION sales_summary_func() RETURNS trigger AS {mathJaxContainer[5]} LANGUAGE plpgsql;

步骤3：创建汇总触发器

CREATE TRIGGER sales_summary_trigger
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW
EXECUTE FUNCTION sales_summary_func();

步骤4：测试汇总效果

-- 插入销售数据
INSERT INTO sales_fact VALUES (20240101, 1, 100.00, 10);
INSERT INTO sales_fact VALUES (20240101, 2, 200.00, 20);
INSERT INTO sales_fact VALUES (20240102, 3, 150.00, 15);

-- 查看汇总表（20240101的销售额=300，销售量=30）
SELECT * FROM sales_summary_bytime;

-- 删除一条销售数据
DELETE FROM sales_fact WHERE product_key = 1;

-- 查看汇总表（20240101的销售额=200，销售量=20）
SELECT * FROM sales_summary_bytime;

案例5：运维场景——DDL事件监控（事件触发器）

需求：监控数据库的DDL操作（比如建表、删表、改表），输出提示信息，防止误操作。

步骤1：编写事件触发器函数

CREATE OR REPLACE FUNCTION ddl_monitor_func() RETURNS event_trigger AS {mathJaxContainer[6]} LANGUAGE plpgsql;

步骤2：创建事件触发器

-- 监控所有DDL命令开始时的操作
CREATE EVENT TRIGGER ddl_monitor_trigger
ON ddl_command_start
EXECUTE FUNCTION ddl_monitor_func();

步骤3：测试DDL监控

-- 创建测试表（会触发监控）
CREATE TABLE test (id integer);

-- 修改测试表（会触发监控）
ALTER TABLE test ADD COLUMN name text;

-- 删除测试表（会触发监控）
DROP TABLE test;

运行后会看到类似提示：监控到DDL操作：事件=ddl_command_start，命令=CREATE TABLE。

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

PL/pgSQL 入门教程（五）：触发器