Postgresql 中文操作指南

CREATE TRIGGER

CREATE TRIGGER - 定义一个新触发器

Synopsis

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

Description

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 的触发器仅针对任何给定的操作执行一次，无论其修改多少行（尤其是，修改 0 行的操作仍会造成任何适用的 FOR EACH STATEMENT 触发器被执行）。

指定为在 INSTEAD OF 触发器事件之前执行的触发器必须标记为 FOR EACH ROW ，并且只能在视图上定义。视图上的 BEFORE 和 AFTER 触发器必须标记为 FOR EACH STATEMENT 。

此外，触发器可以被定义为在 TRUNCATE 之前执行，但只能是 FOR EACH STATEMENT 。

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

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

如果为同一事件定义了同种类型的多个触发器，它们将按照名称的字母顺序执行。

如果指定 CONSTRAINT 选项，此命令将创建一个 constraint trigger 。这与常规触发器相同，除非可以使用 SET CONSTRAINTS 调整触发器触发的时间。约束触发器必须是对普通表（非外部表）的 AFTER ROW 触发器。它们可以触发语句结尾引发触发事件或包含事务结尾；在后一种情况下称之为 deferred 。也可通过 SET CONSTRAINTS 使用 constraint trigger 立即执行挂起的延迟触发。当所实现的约束被违反时，约束触发器有望引发异常。

REFERENCING 选项允许收集 transition relations ，这些 transition relations 是包含当前 SQL 语句插入、删除或修改的所有行的行集。此功能让触发器可以查看该语句的全局视图，而不仅仅是一次查看一行。此选项仅允许用于不是约束触发器的 AFTER 触发器；此外，如果触发器是 UPDATE 触发器，它不能指定 column_name 列表。 OLD TABLE 只能指定一次，且只能用于可以在 UPDATE 或 DELETE 上触发的触发器；它将创建一个过渡关系，其中包含语句更新或删除的所有行的 before-images 。类似地， NEW TABLE 只能指定一次，并且只能用于可以在 UPDATE 或 INSERT 上触发的触发器；它将创建一个过渡关系，其中包含语句更新或插入的所有行的 after-images 。

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

请参阅 Chapter 39 以了解有关触发器的更多信息。

Parameters

  • 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 。

    • condition

  • 布尔表达式，用来确定是否实际执行触发器函数。如果指定 WHEN ，则只有在 condition 返回 true 时函数才会被调用。在 FOR EACH ROW 触发器中， WHEN 条件可以通过编写 OLD._column_name or NEW._column_name respectively. Of course, INSERT 来引用旧行和/或新行值列； OLD 触发器无法引用 DELETE ， NEW 触发器无法引用 INSTEAD OF 。

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

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

  • 请注意，对于约束触发器，不会延迟评估 WHEN 条件，而是在执行行更新操作后立即评估。如果条件评估结果不是 true，则不会将触发器排队以进行延迟执行。

    • function_name

  • 用户提供的函数，声明不接受任何参数且返回类型为 trigger ，当触发器触发时执行。

  • 在 CREATE TRIGGER 的语法中，关键字 FUNCTION 和 PROCEDURE 是等效的，但引用的函数无论如何必须是函数而不是过程。在此处使用关键字 PROCEDURE 属于历史遗留问题，并且已弃用。

    • arguments

  • 可选择用逗号分隔的参数列表，在执行触发器时提供给函数。参数是文本字符串常量。简单名称和数字常量也可以在此处编写，但是它们都会转换为字符串。请查看触发器函数的实现语言的说明，以找出如何在函数中访问这些参数；它可能不同于普通函数参数。

Notes

要创建或替换表上的触发器，用户必须具有 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 条件未返回真值时，无需排队事件或在语句末尾重新提取行。如果只需要对少数行触发器后，此操作可以显著加快修改了多行的语句的速度。

在某些情况下，单个 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 选项。

不建议在已经对触发器的表执行过更新操作的事务中替换现有的触发器。已做出的触发触发决策或触发决策的部分不会被再次考虑，因此效果可能会令人惊讶。

有一些内置的触发器函数可以用来解决常见问题，无需编写触发器代码；请参阅 Section 9.28 。

Examples

每当对表 accounts 的行进行更新时执行函数 check_account_update ：

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

修改该触发器定义，仅在 UPDATE 命令中指定列 balance 作为目标时才执行此函数：

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();

Section 39.4 中包含一个用 C 编写的触发器函数的完整示例。

Compatibility

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

SQL 规定应该按照创建时间顺序触发多个触发器。PostgreSQL 使用名称顺序，这被认为更方便。

SQL 规定，在级联删除中对 BEFORE DELETE 触发器触发 after 级联 DELETE 完成。PostgreSQL 行为是 BEFORE DELETE 总是先于删除操作触发，即使是级联操作。这被认为更一致。如果 BEFORE 触发器在由引用操作引起的更新期间修改行或阻止更新，也会有非标准行为。这可能导致约束冲突或存储的数据不符合引用约束。

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

为 TRUNCATE 触发触发器的能力是 SQL 标准中 PostgreSQL 扩展，定义视图中语句级触发器的能力也是如此。

CREATE CONSTRAINT TRIGGER 是 SQL 标准中 PostgreSQL 扩展。 OR REPLACE 选项也是如此。

See Also

ALTER TRIGGER ， DROP TRIGGER ， CREATE FUNCTION ， SET CONSTRAINTS