CREATE TRIGGER
CREATE TRIGGER — 定义新触发器
概要
CREATE [ OR REPLACE ] [ 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 )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
描述
CREATE TRIGGER
创建一个新触发器。CREATE OR REPLACE TRIGGER
将创建一个新触发器,或替换一个现有触发器。触发器将与指定的表、视图或外部表关联,并在对该表执行某些操作时执行指定的函数*function_name
*。
要替换现有触发器的当前定义,请使用CREATE OR REPLACE TRIGGER
,指定现有触发器的名称和父表。所有其他属性都将被替换。
可以指定触发器在尝试对行执行操作之前(在检查约束并尝试INSERT
、UPDATE
或DELETE
之前)触发;或在操作完成后(在检查约束并完成INSERT
、UPDATE
或DELETE
之后)触发;或在操作替代(在视图上插入、更新或删除的情况下)。如果触发器在事件之前或替代事件触发,则触发器可以跳过当前行的操作,或更改正在插入的行(仅适用于INSERT
和UPDATE
操作)。如果触发器在事件之后触发,则所有更改(包括其他触发器的影响)对触发器都是“可见”的。
标记为FOR EACH ROW
的触发器针对操作修改的每一行调用一次。例如,影响 10 行的DELETE
将导致目标关系上的任何ON DELETE
触发器被调用 10 次,每次针对一行已删除的行。相反,标记为FOR EACH STATEMENT
的触发器仅针对任何给定操作执行一次,无论其修改多少行(特别是,修改零行的操作仍将导致执行任何适用的FOR EACH STATEMENT
触发器)。
指定为在触发器事件INSTEAD OF
触发的触发器必须标记为FOR EACH ROW
,并且只能在视图上定义。视图上的BEFORE
和AFTER
触发器必须标记为FOR EACH STATEMENT
。
此外,可以定义触发器针对TRUNCATE
触发,但只能FOR EACH STATEMENT
。
下表总结了哪些类型的触发器可用于表、视图和外部表
何时 | 事件 | 行级 | 语句级 |
---|---|---|---|
BEFORE | INSERT /UPDATE /DELETE | 表和外部表 | 表、视图和外部表 |
TRUNCATE | — | 表和外部表 | |
AFTER | INSERT /UPDATE /DELETE | 表和外部表 | 表、视图和外部表 |
TRUNCATE | — | 表和外部表 | |
INSTEAD OF | INSERT /UPDATE /DELETE | 视图 | — |
TRUNCATE | — | — |
此外,触发器定义可以指定布尔WHEN
条件,该条件将经过测试以查看是否应触发触发器。在行级触发器中,WHEN
条件可以检查行的列的旧值和/或新值。语句级触发器也可以有WHEN
条件,尽管该特性对它们来说不太有用,因为该条件无法引用表中的任何值。
如果为同一事件定义了多种同类触发器,它们将按名称按字母顺序触发。
当指定CONSTRAINT
选项时,此命令将创建一个约束触发器。这与常规触发器相同,除了可以使用SET CONSTRAINTS
调整触发器触发的时机。约束触发器必须是普通表(非外部表)上的AFTER ROW
触发器。它们可以在导致触发事件的语句结束时或在包含事务结束时触发;在后一种情况下,它们被称为延迟。还可以使用SET CONSTRAINTS
立即强制执行挂起的延迟触发器。当违反约束触发器实施的约束时,预计约束触发器将引发异常。
REFERENCING
选项允许收集转换关系,这些关系是包含当前 SQL 语句插入、删除或修改的所有行的行集。此功能可以让触发器看到语句的全局视图,而不仅仅是一次一行。此选项仅允许用于不是约束触发器的AFTER
触发器;此外,如果触发器是UPDATE
触发器,则它不能指定*column_name
列表。OLD TABLE
只能指定一次,并且只能用于可以在UPDATE
或DELETE
上触发的触发器;它创建一个转换关系,其中包含语句更新或删除的所有行的之前映像*。同样,NEW TABLE
只能指定一次,并且只能用于可以在UPDATE
或INSERT
上触发的触发器;它创建一个转换关系,其中包含语句更新或插入的所有行的之后映像。
SELECT
不修改任何行,因此无法创建SELECT
触发器。规则和视图可以为看似需要SELECT
触发器的问题提供可行的解决方案。
有关触发器的更多信息,请参阅第 39 章。
参数
name
要赋予新触发器的名称。此名称必须与同一表的任何其他触发器的名称不同。名称不能是模式限定的——触发器继承其表的模式。对于约束触发器,这也是使用
SET CONSTRAINTS
修改触发器行为时要使用的名称。BEFORE
AFTER
INSTEAD OF
确定函数是在事件之前、之后还是代替事件调用的。约束触发器只能指定为
AFTER
。event
在
INSERT
、UPDATE
、DELETE
或TRUNCATE
中选择一个;这指定触发器将触发的事件。可以使用OR
指定多个事件,但请求转换关系时除外。对于
UPDATE
事件,可以使用此语法指定列列表UPDATE OF
column_name1
[,column_name2
... ]仅当至少一个列被提及为
UPDATE
命令的目标,或当其中一个列是生成列(取决于UPDATE
的目标列)时,触发器才会触发。INSTEAD OF UPDATE
事件不允许列列表。请求转换关系时也不能指定列列表。table_name
触发器所属的表、视图或外部表的名称(可选,可以限定架构)。
referenced_table_name
约束引用的另一个表(可能限定架构)的名称。此选项用于外键约束,不建议一般使用。仅可为约束触发器指定此选项。
DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED
触发器的默认时机。有关这些约束选项的详细信息,请参阅 CREATE TABLE 文档。仅可为约束触发器指定此选项。
REFERENCING
此关键字紧接在声明一个或两个关系名称之前,这些关系名称提供对触发语句的转换关系的访问权限。
OLD TABLE
NEW TABLE
此子句指示以下关系名称是用于映像转换关系还是用于后映像转换关系。
transition_relation_name
在此转换关系中用于触发器内的(不限定)名称。
FOR EACH ROW
FOR EACH STATEMENT
这指定触发器函数应针对触发器事件影响的每一行触发一次,还是仅针对每个 SQL 语句触发一次。如果未指定任何选项,则
FOR EACH STATEMENT
为默认选项。约束触发器只能指定FOR EACH ROW
。条件
确定触发器函数是否实际执行的布尔表达式。如果指定
WHEN
,则仅当condition
返回true
时才调用函数。在FOR EACH ROW
触发器中,WHEN
条件可以通过分别编写OLD.
或column_name
NEW.
来引用旧行和/或新行值中的列。当然,column_name
INSERT
触发器无法引用OLD
,DELETE
触发器无法引用NEW
。INSTEAD OF
触发器不支持WHEN
条件。目前,
WHEN
表达式不能包含子查询。请注意,对于约束触发器,
WHEN
条件的评估不会延迟,而是在执行行更新操作后立即发生。如果条件未评估为 true,则不会将触发器排队进行延迟执行。function_name
用户提供的函数,声明为不接受任何参数并返回类型
trigger
,在触发器触发时执行。在
CREATE TRIGGER
的语法中,关键字FUNCTION
和PROCEDURE
是等效的,但引用的函数无论如何都必须是函数,而不是过程。此处使用关键字PROCEDURE
是历史遗留问题,已被弃用。arguments
在执行触发器时提供给函数的可选逗号分隔参数列表。参数是字符串常量。简单名称和数字常量也可以写在这里,但它们都将转换为字符串。请查看触发器函数的实现语言的描述,以了解如何在函数中访问这些参数;它可能与普通函数参数不同。
注释
要在表上创建或替换触发器,用户必须具有该表的TRIGGER
权限。用户还必须对触发器函数具有EXECUTE
权限。
使用DROP TRIGGER
删除触发器。
在分区表上创建行级触发器会导致在每个现有分区上创建相同的“克隆”触发器;并且稍后创建或附加的任何分区也将具有相同的触发器。如果子分区上已存在同名触发器,则会发生错误,除非使用CREATE OR REPLACE TRIGGER
,在这种情况下,该触发器将被克隆触发器替换。当分区从其父分区分离时,其克隆触发器将被删除。
列特定触发器(使用UPDATE OF*
column_name*
语法定义的触发器)将在其任何列在UPDATE
命令的SET
列表中列为目标时触发。即使未触发触发器,列的值也有可能发生更改,因为BEFORE UPDATE
触发器对行内容所做的更改不被考虑。相反,诸如UPDATE ... SET x = x ...
的命令将触发列x
上的触发器,即使该列的值没有更改。
在BEFORE
触发器中,WHEN
条件在函数被执行或将被执行之前进行评估,因此使用WHEN
与在触发器函数开头测试相同条件在实质上没有区别。特别注意,条件看到的NEW
行是当前值,可能被较早的触发器修改过。此外,BEFORE
触发器的WHEN
条件不允许检查NEW
行的系统列(例如ctid
),因为这些列尚未设置。
在AFTER
触发器中,WHEN
条件在行更新发生后立即评估,并确定是否将事件排队以在语句结束时触发触发器。因此,当AFTER
触发器的WHEN
条件未返回 true 时,无需排队事件,也不必在语句结束时重新获取行。如果仅需对几行触发触发器,则这可能会极大地加快修改多行的语句的速度。
在某些情况下,单个 SQL 命令有可能触发多种类型的触发器。例如,带有ON CONFLICT DO UPDATE
子句的INSERT
可能会导致插入和更新操作,因此它会根据需要触发两种类型的触发器。提供给触发器的转换关系特定于其事件类型;因此,INSERT
触发器将只看到已插入的行,而UPDATE
触发器将只看到已更新的行。
由外键强制操作(例如ON UPDATE CASCADE
或ON DELETE SET NULL
)导致的行更新或删除将被视为导致它们的 SQL 命令的一部分(请注意,此类操作永远不会被延迟)。受影响表上的相关触发器将被触发,因此这提供了另一种 SQL 命令可能触发与其类型不直接匹配的触发器的方式。在简单的情况下,请求转换关系的触发器会将单个原始 SQL 命令在其表中导致的所有更改视为单个转换关系。但是,在某些情况下,存在请求转换关系的AFTER ROW
触发器会导致由单个 SQL 命令触发的外键强制操作被拆分为多个步骤,每个步骤都有自己的转换关系。在这种情况下,任何存在的语句级触发器都将在创建转换关系集时触发一次,确保触发器在转换关系中仅看到每个受影响的行一次。
视图上的语句级触发器仅在视图上的操作由行级INSTEAD OF
触发器处理时才触发。如果操作由INSTEAD
规则处理,则规则发出的任何语句都将代替命名视图的原始语句执行,因此将触发的触发器是替换语句中命名的表上的触发器。类似地,如果视图是自动可更新的,则操作将通过自动将语句重写为视图基本表上的操作来处理,因此基本表的语句级触发器将被触发。
修改分区表或具有继承子表的表会触发附加到显式命名的表的语句级触发器,但不会触发其分区或子表的语句级触发器。相比之下,行级触发器会在受影响分区或子表中的行上触发,即使它们在查询中没有被显式命名。如果语句级触发器已被定义为由REFERENCING
子句命名的转换关系,那么所有受影响分区或子表中的行的前后映像都是可见的。在继承子表的情况下,行映像仅包括触发器附加到的表中存在的列。
目前,无法在分区或继承子表上定义具有转换关系的行级触发器。此外,分区表上的触发器可能不是INSTEAD OF
。
当前,OR REPLACE
选项不支持约束触发器。
不建议在已对触发器的表执行更新操作的事务中替换现有触发器。触发器触发决策或触发器触发决策的部分(如果已做出)将不会被重新考虑,因此其影响可能会令人惊讶。
有一些内置触发器函数可用于解决常见问题,而无需编写自己的触发器代码;请参阅第 9.28 节。
示例
每当表accounts
的一行即将更新时,执行函数check_account_update
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
修改该触发器定义,仅当列balance
在UPDATE
命令中指定为目标时才执行该函数
CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();
此表单仅在列balance
实际已更改值时才执行该函数
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();
调用函数来记录accounts
的更新,但仅在有更改时
CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_account_update();
为要插入到视图基础表中的每一行执行函数view_insert_row
CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION view_insert_row();
为每个语句执行函数check_transfer_balances_to_zero
,以确认transfer
行偏移量为零
CREATE TRIGGER transfer_insert
AFTER INSERT ON transfer
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE FUNCTION check_transfer_balances_to_zero();
为每一行执行函数check_matching_pairs
,以确认同时(通过同一语句)对匹配对进行更改
CREATE TRIGGER paired_items_update
AFTER UPDATE ON paired_items
REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
FOR EACH ROW
EXECUTE FUNCTION check_matching_pairs();
第 39.4 节包含用 C 编写的触发器函数的完整示例。
兼容性
在PostgreSQL中,CREATE TRIGGER
语句实现了SQL标准的子集。目前缺少以下功能
虽然
AFTER
触发器的转换表名称使用REFERENCING
子句以标准方式指定,但FOR EACH ROW
触发器中使用的行变量不能在REFERENCING
子句中指定。它们以取决于触发器函数所写语言的方式提供,但对于任何一种语言都是固定的。一些语言有效地表现得好像有一个REFERENCING
子句,其中包含OLD ROW AS OLD NEW ROW AS NEW
。该标准允许将转换表与特定于列的
UPDATE
触发器一起使用,但转换表中应可见的行集取决于触发器的列列表。这目前尚未由 PostgreSQL 实现。PostgreSQL 仅允许为触发操作执行用户定义函数。该标准允许执行其他一些 SQL 命令,例如
CREATE TABLE
,作为触发操作。通过创建一个执行所需命令的用户定义函数,可以轻松解决此限制。
SQL 规定应按创建时间顺序触发多个触发器。PostgreSQL使用名称顺序,这被认为更方便。
SQL 规定,级联删除上的BEFORE DELETE
触发器在级联DELETE
完成之后触发。对于PostgreSQL,BEFORE DELETE
的行为是始终在删除操作之前触发,即使是级联删除。这被认为更一致。如果BEFORE
触发器在由引用操作导致的更新期间修改行或阻止更新,也会出现非标准行为。这可能导致约束冲突或不遵守引用约束的存储数据。
使用OR
为单个触发器指定多个操作的能力是PostgreSQL对 SQL 标准的扩展。
为TRUNCATE
触发触发器以及在视图上定义语句级触发器的能力是PostgreSQL对 SQL 标准的扩展。
CREATE CONSTRAINT TRIGGER
是PostgreSQL对SQL标准的扩展。OR REPLACE
选项也是如此。