9.3 9.4 9.5 9.6 10 11 12
阿里云PostgreSQL 问题报告 纠错本页面
CREATE TRIGGER
上一页 上一级SQL 命令起始页 下一页

CREATE TRIGGER

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 PROCEDURE function_name ( arguments )

这里的event可以是下列之一：

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

描述

CREATE TRIGGER创建一个新触发器。该触发器将被关联到指定的表、视图或者外部表并且在某些操作在该表上执行时将执行指定的函数function_name。

该触发器可以被指定为在一行上尝试该操作之前触发（在约束被检查并且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。

下面的表格总结了哪些触发器类型可以被用在表、视图和外部表上：

何时事件行级语句级
BEFOREINSERT/UPDATE/DELETE表和外部表表、视图和外部表
TRUNCATE—表
AFTERINSERT/UPDATE/DELETE表和外部表表、视图和外部表
TRUNCATE—表
INSTEAD OFINSERT/UPDATE/DELETE视图—
TRUNCATE——

还有，一个触发器定义可以指定一个布尔的WHEN条件，它将被测试来看看该触发器是否应该被触发。在行级触发器中，WHEN条件可以检查该行的列的新旧值。语句级触发器也可以有WHEN条件，尽管该特性对于它们不是很有用（因为条件不能引用表中的任何值）。

如果有多个同种触发器被定义为相同事件触发，它们将按照名称的字母表顺序被触发。

当CONSTRAINT选项被指定，这个命令会创建一个约束触发器。这和一个常规触发器相同，不过触发该触发器的时机可以使用SET CONSTRAINTS调整。约束触发器必须是普通表（不是外部表）上的AFTER ROW触发器。它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被延迟。一个待处理的延迟触发器的引发也可以使用SET CONSTRAINTS立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。

REFERENCING选项可以收集转换关系， 它们是包含由当前SQL语句插入、删除或修改的所有行的行集。 此功能可让触发器查看语句所做的操作的全局视图，而不是一次一行。 该选项仅适用于不是约束触发器的AFTER触发器；另外， 如果触发器是一个UPDATE触发器，它不能指定一个 column_name列表。 OLD TABLE只能指定一次，并且只能用于触发UPDATE 或DELETE的触发器；它会创建一个包含由语句更新或删除的所有行的 before-images的转换关系。同样，NEW TABLE只能指定一次， 并且只能针对可以在UPDATE或INSERT上触发的触发器； 它会创建一个包含由语句更新或插入的所有行的after-images的转换关系。

SELECT不修改任何行，因此你无法创建SELECT触发器。 规则和视图可以为似乎需要SELECT触发器的问题提供可行的解决方案。

关于触发器的更多信息请见第 38 章。

参数

name

给新触发器的名称。这必须与同一个表上的任何其他触发器相区别。名称不能是模式限定的 — 该触发器会继承它所在表的模式。对于一个约束触发器，这也是使用SET CONSTRAINTS修改触发器行为时要用到的名字。

BEFORE
AFTER
INSTEAD OF

决定该函数是要在事件之前、之后被调用还是会取代该事件。一个约束触发器也能被指定为AFTER。

event

INSERT、UPDATE、DELETE或者TRUNCATE之一，这指定了将要引发该触发器的事件。多个事件可以用OR指定， 除非要求转换关系。

对于UPDATE事件，可以使用下面的语法指定一个列的列表： 

UPDATE OF column_name1 [, column_name2 ... ]

只有当至少一个被列出的列出现在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。

condition

一个决定该触发器函数是否将被实际执行的布尔表达式。如果指定了WHEN，只有condition返回true时才会调用该函数。在FOR EACH ROW触发器中，WHEN条件可以分别写OLD.column_name或者NEW.column_name来引用列的新旧行值。当然，INSERT触发器不能引用OLD并且DELETE触发器不能引用NEW。

INSTEAD OF触发器不支持WHEN条件。

当前，WHEN表达式不能包含子查询。

注意对于约束触发器，对于WHEN条件的计算不会被延迟，而是直接在行更新操作被执行之后立刻发生。如果该条件计算得不到真，那么该触发器就不会被放在延迟执行的队列中。

function_name

一个用户提供的函数，它被声明为不用参数并且返回类型trigger，当触发器引发时会执行该函数。

arguments

一个可选的逗号分隔的参数列表，它在该触发器被执行时会被提供给该函数。参数是字符串常量。简单的名称和数字常量也可以被写在这里，但是它们将全部被转换成字符串。请检查该触发器函数的实现语言的描述来找出在函数内部如何访问这些参数，这可能与普通函数参数不同。

注解

要在一个表上创建一个触发器，用户必须具有该表上的TRIGGER特权。用户还必须具有在触发器函数上的EXECUTE特权。

使用DROP TRIGGER移除一个触发器。

当一个列相关的触发器（使用UPDATE OF column_name语法定义的触发器）的列被列为UPDATE命令的SET列表目标时，它会被触发。即便该触发器没有被引发，一个列的值也可能改变，因为BEFORE UPDATE触发器对行内容所作的改变不会被考虑。相反，一个诸如UPDATE ... SET x = x ...的命令将引发一个位于列x上的触发器，即便该列的值没有改变。

在一个BEFORE触发器中，WHEN条件正好在函数被或者将被执行之前被计算，因此使用WHEN与在触发器函数的开始测试同一个条件没有实质上的区别。特别注意该条件看到的NEW行是当前值，虽然可能已被早前的触发器所修改。还有，一个BEFORE触发器的WHEN条件不允许检查NEW行的系统列（例如oid），因为那些列还没有被设置。

在一个AFTER触发器中，WHEN条件正好在行更新发生之后被计算，并且它决定一个事件是否要被放入队列以便在语句的末尾引发该触发器。因此当一个AFTER触发器的WHEN条件不返回真时，没有必要把一个事件放入队列或者在语句末尾重新取得该行。如果触发器只需要为一些行被引发，就能够显著地加快修改很多行的语句的速度。

在某些情况下，单个SQL命令可能触发多种触发器。例如，一个带有 ON CONFLICT DO UPDATE子句的INSERT 可能会导致插入和更新操作，所以它会根据需要触发两种触发器。 提供给触发器的转换关系特定于其事件类型；因此一个INSERT 触发器将只能看到插入的行，而一个UPDATE触发器将只能看到更新的行。

由外键执行操作（例如ON UPDATE CASCADE或ON DELETE SET NULL） 引起的行更新或删除被视为导致它们的SQL命令的一部分（请注意，这样的操作从未推迟）。 受影响的表上的相关触发器将被触发， 这样可以提供SQL命令可能触发不直接匹配其类型的触发器的另一种方式。在简单情况下， 请求转换关系的触发器会将单个原始SQL命令在其表中引起的所有更改视为单个转换关系。 但是，在某些情况下，请求转换关系的AFTER ROW 触发器的存在会导致由单个SQL命令触发的外键执行操作分为多个步骤， 每个步骤都有自己的转换关系。在这种情况下，每创建一个转换关系集时， 将触发一次任何存在的语句级触发器，以确保触发器在转换关系中查看每个受影响的行一次且仅一次。

使用继承子表修改分区表或表会触发直接附加到该表的语句级触发器， 但不触发其分区或子表的语句级触发器。相比之下， 所有受影响的分区或子表都会触发行级触发器。如果语句级触发器已用由 REFERENCING子句命名的转换关系定义， 则在所有受影响的分区或子表中可以看到行的之前图像和之后图像。在继承子表的情况下， 行图像仅包含触发器附加到的表中存在的列。目前， 无法在分区或继承子表上定义具有转换关系的行级触发器。

在PostgreSQL 7.3 以前的版本中，必须要声明触发器函数为返回占位符类型opaque而不是trigger。要支持载入旧的转储文件，CREATE TRIGGER将接受一个被声明为返回opaque的函数，但是它会发出一个通知并且会把该函数的声明返回类型改为trigger。

例子

只要表accounts的一行即将要被更新时会执行函数check_account_update： 

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE PROCEDURE check_account_update();

下面的例子与上面一个例子相同，但是只在UPDATE命令指定要更新balance列时才执行该函数： 

CREATE TRIGGER check_update
    BEFORE UPDATE OF balance ON accounts
    FOR EACH ROW
    EXECUTE PROCEDURE check_account_update();

这种形式只有列balance具有真正被改变的值时才执行该函数： 

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
    EXECUTE PROCEDURE check_account_update();

调用一个函数来记录accounts的更新，但是只在有东西被改变时才调用： 

CREATE TRIGGER log_update
    AFTER UPDATE ON accounts
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE log_account_update();

为每一个要插入到视图底层表中的行执行函数view_insert_row： 

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE PROCEDURE 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 PROCEDURE 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 PROCEDURE check_matching_pairs();

第 38.4 节包含一个用 C 编写的触发器函数的完整例子。

兼容性

PostgreSQL中的CREATE TRIGGER语句实现了SQL标准的一个子集。目前缺少下列功能：

  • 虽然AFTER触发器的转换表名称是使用标准方式的REFERENCING 子句指定的，但可以不在REFERENCING子句中指定FOR EACH ROW 触发器中使用的行变量。它们的可用方式取决于编写触发器函数的语言， 但对于任何一种语言都是固定的。有些语言的行为好像有一个包含 OLD ROW AS OLD NEW ROW AS NEW的REFERENCING子句。

  • 该标准允许转换表与列特定的UPDATE触发器一起使用， 但是那些在转换表中应该可见的行集取决于触发器的列列表。 这当前不是由PostgreSQL实现的。

  • PostgreSQL只允许为被触发动作执行一个用户定义的函数。标准允许执行许多其他的 SQL 命令作为被触发的动作，例如CREATE TABLE。这种限制可以很容易地通过创建一个执行想要的命令的用户定义函数来绕过。

SQL 指定多个触发器应该以被创建时间的顺序触发。PostgreSQL则使用名称顺序，这被认为更加方便。

SQL 指定级联删除上的BEFORE DELETE触发器在级联的DELETE完成之后引发。PostgreSQL的行为则是BEFORE DELETE总是在删除动作之前引发，即使是一个级联删除。这被认为更加一致。 如果BEFORE触发器修改行或者在引用动作引起的更新期间阻止更新，这也是非标准行为。这能导致约束违背或者被存储的数据不遵从引用约束。

使用OR为一个单一触发器指定多个动作的能力是 SQL 标准的一个PostgreSQL扩展。

为TRUNCATE引发触发器的能力是 SQL 标准的一个PostgreSQL扩展，在视图上定义语句级触发器的能力也是一样。

CREATE CONSTRAINT TRIGGER是SQL标准的一个PostgreSQL扩展。

参见

ALTER TRIGGER, DROP TRIGGER, CREATE FUNCTION, SET CONSTRAINTS
上一页 上一级 下一页
CREATE TRANSFORM 起始页 CREATE TYPE