PostgreSQL的触发器使用注意事项

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
触发器操作

触发器的操作通过SQL DDL语句进行，包括 CREATE|ALTER|DROP TRIGGER ，以及 ALTER TABLE ENABLE|DISABLE TRIGGER 进行。注意PostgreSQL内部的约束是通过触发器实现的。
CREATE TRIGGER 可以用于创建触发器。
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
event包括：
    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE
DROP TRIGGER 用于移除触发器。
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
ALTER TRIGGER 用于修改触发器定义，注意这里只能修改触发器名，以及其依赖的扩展。
ALTER TRIGGER name ON table_name RENAME TO new_name
ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name





    
启用禁用触发器，修改触发模式是通过ALTER TABLE的子句实现的。
ALTER TABLE  包含了一系列触发器修改的子句：
ALTER TABLE tbl ENABLE TRIGGER tgname; -- 设置触发模式为O (本地连接写入触发，默认)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- 设置触发模式为R (复制连接写入触发)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- 设置触发模式为A (总是触发)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- 设置触发模式为D (禁用)
注意这里在ENABLE与DISABLE触发器时，可以指定用USER替换具体的触发器名称，这样可以只禁用用户显式创建的触发器，不会把系统用于维持约束的触发器也禁用了。
ALTER TABLE tbl_name DISABLE TRIGGER USER; -- 禁用所有用户定义的触发器，系统触发器不变  
ALTER TABLE tbl_name DISABLE TRIGGER ALL;  -- 禁用所有触发器
ALTER TABLE tbl_name ENABLE TRIGGER USER;  -- 启用所有用户定义的触发器
ALTER TABLE tbl_name ENABLE TRIGGER ALL;   -- 启用所有触发器
获取表上的触发器
最简单的方式当然是psql的\d+ tablename。但这种方式只会列出用户创建的触发器，不会列出与表上约束相关联的触发器。直接查询系统目录pg_trigger，并通过tgrelid用表名过滤
SELECT * FROM pg_trigger WHERE tgrelid = 'tbl_name'::RegClass;
获取触发器定义
pg_get_triggerdef(trigger_oid oid)函数可以给出触发器的定义。
该函数输入参数为触发器OID，返回创建触发器的SQL DDL语句。
SELECT pg_get_triggerdef(oid) FROM pg_trigger; -- WHERE xxx
触发器视图
pg_trigger (中文) 提供了系统中触发器的目录
触发器类型
触发器类型tgtype包含了触发器触发条件相关信息：BEFORE|AFTER|INSTEAD OF, INSERT|UPDATE|DELETE|TRUNCATE
TRIGGER_TYPE_ROW         (1 << 0)  // [0] 0:语句级 	1:行级
TRIGGER_TYPE_BEFORE      (1 << 1)  // [1] 0:AFTER 	1:BEFORE
TRIGGER_TYPE_INSERT      (1 << 2)  // [2] 1: INSERT
TRIGGER_TYPE_DELETE      (1 << 3)  // [3] 1: DELETE
TRIGGER_TYPE_UPDATE      (1 << 4)  // [4] 1: UPDATE
TRIGGER_TYPE_TRUNCATE    (1 << 5)  // [5] 1: TRUNCATE
TRIGGER_TYPE_INSTEAD     (1 << 6)  // [6] 1: INSTEAD OF 
触发器模式
触发器tgenabled字段控制触发器的工作模式，参数session_replication_role 可以用于配置触发器的触发模式。该参数可以在会话层级更改，可能的取值包括：origin(default),replica,local。
(D)isable触发器永远不会被触发，(A)lways触发器在任何情况下触发， (O)rigin触发器会在origin|local模式触发（默认），而 (R)eplica触发器replica模式触发。R触发器主要用于逻辑复制，例如pglogical的复制连接就会将会话参数session_replication_role设置为replica，而R触发器只会在该连接进行的变更上触发。
ALTER TABLE tbl ENABLE TRIGGER tgname; -- 设置触发模式为O (本地连接写入触发，默认)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- 设置触发模式为R (复制连接写入触发)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- 设置触发模式为A (始终触发)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- 设置触发模式为D (禁用)
在information_schema中还有两个触发器相关的视图：information_schema.triggers, information_schema.triggered_update_columns，表过不提。
触发器FAQ
触发器可以建在哪些类型的表上？
普通表（分区表主表，分区表分区表，继承表父表，继承表子表），视图，外部表。
触发器的类型限制
视图上不允许建立BEFORE与AFTER触发器（不论是行级还是语句级）
视图上只能建立INSTEAD OF触发器，INSERTEAD OF触发器也只能建立在视图上，且只有行级，不存在语句级INSTEAD OF触发器。
INSTEAD OF` 触发器只能定义在视图上，并且只能使用行级触发器，不能使用语句级触发器。
触发器与锁
在表上创建触发器会先尝试获取表级的Share Row Exclusive Lock。这种锁会阻止底层表的数据变更，且自斥。因此创建触发器会阻塞对表的写入。
触发器与COPY的关系
COPY只是消除了数据解析打包的开销，实际写入表中时仍然会触发触发器，就像INSERT一样。
    ←Previous
    Next→