PostgreSQL视图BEFORE/AFTER语句级触发器失效问题求解

背景与问题重现 #

我创建了以下数据库对象：

• 名为person的表
• 名为log的表，已插入1行初始数据（num字段值为0）
• 名为my_v的视图，基于person表创建
• 名为my_func()的触发器函数，作用是将log.num的值加1
• 尝试创建my_t触发器：在my_v上的每个INSERT/UPDATE/DELETE语句执行前触发my_func()

对应的创建SQL代码如下：

CREATE TABLE person (
  name VARCHAR(20)
);

CREATE TABLE log (
  num INTEGER
);
INSERT INTO log (num) VALUES (0);

CREATE VIEW my_v AS
  SELECT * FROM person;

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
  UPDATE log SET num = num + 1;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_t BEFORE INSERT OR UPDATE OR DELETE ON my_v
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

我也测试了将触发器设置为语句执行后触发的情况：

CREATE TRIGGER my_t AFTER INSERT OR UPDATE OR DELETE ON my_v
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

预期log表中的num字段会统计my_func()被触发器调用的次数，但对my_v执行插入、更新、删除操作后，log.num始终为0，执行语句及结果如下：

postgres=# INSERT INTO my_v (name) VALUES ('John');
INSERT 0 1 /* -> 成功插入1行 */

postgres=# UPDATE my_v SET name = 'Tom';
UPDATE 1 /* -> 成功更新1行 */

postgres=# DELETE FROM my_v;
DELETE 1 /* -> 成功删除1行 */

postgres=# SELECT num FROM log;
 num
-----
   0
(1 row)

可见PostgreSQL中视图上的BEFORE/AFTER类型INSERT/UPDATE/DELETE触发器无法生效。

注意：使用INSTEAD OF且为FOR EACH ROW的my_t触发器可正常工作：

CREATE TRIGGER my_t INSTEAD OF INSERT OR UPDATE OR DELETE ON my_v
FOR EACH ROW EXECUTE FUNCTION my_func();

但我需要的是语句级的触发，而非行级，请问如何创建可在my_v的INSERT/UPDATE/DELETE操作执行前或后，按语句级触发my_func()的my_t触发器？

解决方案 #

PostgreSQL不支持在视图上创建语句级的BEFORE/AFTER触发器，因为视图本身不存储数据，所有DML操作最终会被解析为对基表的操作。要实现需求，最直接的方案是在**基表person**上创建语句级触发器：

步骤1：删除无效的视图触发器 #

DROP TRIGGER IF EXISTS my_t ON my_v;

步骤2：在基表上创建语句级触发器 #

-- 若需要在语句执行前触发
CREATE TRIGGER my_t BEFORE INSERT OR UPDATE OR DELETE ON person
FOR EACH STATEMENT EXECUTE FUNCTION my_func();

-- 若需要在语句执行后触发
-- CREATE TRIGGER my_t AFTER INSERT OR UPDATE OR DELETE ON person
-- FOR EACH STATEMENT EXECUTE FUNCTION my_func();

验证效果 #

再次执行对my_v的DML操作：

INSERT INTO my_v (name) VALUES ('John');
UPDATE my_v SET name = 'Tom';
DELETE FROM my_v;

查询log表会得到：

postgres=# SELECT num FROM log;
 num
-----
   3
(1 row)

原理说明 #

视图的DML操作本质是转发到基表执行，PostgreSQL仅支持在视图上创建INSTEAD OF行级触发器来拦截操作，但语句级触发器只能作用于实际存储数据的表（基表）。通过在基表上创建语句级触发器，无论操作是直接针对基表还是通过视图执行，都会触发统计逻辑，完美匹配需求。

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito