============== CREATE TRIGGER ============== .. container:: refentry :name: SQL-CREATETRIGGER .. container:: titlepage .. container:: refnamediv .. rubric:: CREATE TRIGGER :name: create-trigger CREATE TRIGGER — 定义一个新触发器 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: synopsis 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 .. container:: refsect1 :name: id-1.9.3.93.6 .. rubric:: 描述 :name: 描述 ``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``\ 。 下面的表格总结了哪些触发器类型可以被用在表、视图和外部表上： .. container:: informaltable :name: SUPPORTED-TRIGGER-TYPES +----------------+----------------------------------+------------+------------------+ | 何时 | 事件 | 行级 | 语句级 | +================+==================================+============+==================+ | ``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``\ 触发器的问题提供可行的解决方案。 .. container:: refsect1 :name: id-1.9.3.93.7 .. rubric:: 参数 :name: 参数 .. container:: variablelist *``name``* 给新触发器的名称。这必须与同一个表上的任何其他触发器相区别。名称不能是模式限定的 — 该触发器会继承它所在表的模式。对于一个约束触发器，这也是使用\ ``SET CONSTRAINTS``\ 修改触发器行为时要用到的名字。 ``BEFORE`` \ ``AFTER`` \ ``INSTEAD OF`` 决定该函数是要在事件之前、之后被调用还是会取代该事件。一个约束触发器也能被指定为\ ``AFTER``\ 。 *``event``* ``INSERT``\ 、\ ``UPDATE``\ 、\ ``DELETE``\ 或者\ ``TRUNCATE``\ 之一，这指定了将要引发该触发器的事件。多个事件可以用\ ``OR``\ 指定，要求传递关系的时候除外。 对于\ ``UPDATE``\ 事件，可以使用下面的语法指定一个列的列表： .. code:: synopsis 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``\ 或者\ ``NEW.column_name``\ 来引用列的新旧行值。当然，\ ``INSERT``\ 触发器不能引用\ ``OLD``\ 并且\ ``DELETE``\ 触发器不能引用\ ``NEW``\ 。 ``INSTEAD OF``\ 触发器不支持\ ``WHEN``\ 条件。 当前，\ ``WHEN``\ 表达式不能包含子查询。 注意对于约束触发器，对于\ ``WHEN``\ 条件的计算不会被延迟，而是直接在行更新操作被执行之后立刻发生。如果该条件计算得不到真，那么该触发器就不会被放在延迟执行的队列中。 *``function_name``* 一个用户提供的函数，它被声明为不用参数并且返回类型\ ``trigger``\ ，当触发器引发时会执行该函数。 在\ ``CREATE TRIGGER``\ 的语法中，关键词\ ``FUNCTION``\ 和\ ``PROCEDURE``\ 是等效的，但是任何情况下被引用的函数必须是一个函数而不是过程。这里，关键词\ ``PROCEDURE``\ 的使用是有历史原因的并且已经被废弃。 *``arguments``* 一个可选的逗号分隔的参数列表，它在该触发器被执行时会被提供给该函数。参数是字符串常量。简单的名称和数字常量也可以被写在这里，但是它们将全部被转换成字符串。请检查该触发器函数的实现语言的描述来找出在函数内部如何访问这些参数，这可能与普通函数参数不同。 .. container:: refsect1 :name: SQL-CREATETRIGGER-NOTES .. rubric:: 注解 :name: 注解 要在一个表上创建一个触发器，用户必须具有该表上的\ ``TRIGGER``\ 特权。用户还必须具有在触发器函数上的\ ``EXECUTE``\ 特权。 使用\ `DROP 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``\ 规则处理，那么该语句发出的任何语句都会代替提及该视图的原始语句执行，这样将被引发的触发器是替换语句中提及的表上的那些触发器。类似地，如果视图是自动可更新的，则该动作将被处理为把该语句自动重写成在视图基表上的一个动作，这样基表的语句级触发器就是要被引发的。 在分区表上创建一个行级触发器将导致在它所有的现有分区上创建相同的触发器，并且以后创建或者挂接的任何分区也将包含一个相同的触发器。分区表上的触发器只能是\ ``AFTER``\ 。 修改分区表或者带有继承子表的表会引发挂接到显式提及表的语句级触发器，但不会引发其分区或子表的语句级触发器。相反，行级触发器会在受影响的分区或子表上引发，即便它们在查询中没有被明确提及。如果一个语句级触发器用\ ``REFERENCING``\ 子句定义有传递关系，则来自所有受影响分区或子表中的行的前后映像都是可见的。在继承子表的情况中，行映像仅包括该触发器所附属的表中存在的列。当前，不能在分区或继承子表上定义带有传递关系的行级触发器。 在PostgreSQL 7.3 以前的版本中，必须要声明触发器函数为返回占位符类型\ ``opaque``\ 而不是\ ``trigger``\ 。要支持载入旧的转储文件，\ ``CREATE TRIGGER``\ 将接受一个被声明为返回\ ``opaque``\ 的函数，但是它会发出一个通知并且会把该函数的声明返回类型改为\ ``trigger``\ 。 .. container:: refsect1 :name: SQL-CREATETRIGGER-EXAMPLES .. rubric:: 例子 :name: 例子 只要表\ ``accounts``\ 的一行即将要被更新时会执行函数\ ``check_account_update``\ ： .. code:: programlisting CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update(); 下面的例子与上面一个例子相同，但是只在\ ``UPDATE``\ 命令指定要更新\ ``balance``\ 列时才执行该函数： .. code:: programlisting CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update(); 这种形式只有列\ ``balance``\ 具有真正被改变的值时才执行该函数： .. code:: programlisting 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``\ 的更新，但是只在有东西被改变时才调用： .. code:: programlisting 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``\ ： .. code:: programlisting CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION view_insert_row(); 为每个语句执行函数\ ``check_transfer_balances_to_zero``\ 以确认\ ``transfer``\ 的行不会有净值增加： .. code:: programlisting 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``\ 以确认（同一个语句）同时对匹配对做了更改 ： .. code:: programlisting 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(); 包含一个用 C 编写的触发器函数的完整例子。 .. container:: refsect1 :name: SQL-CREATETRIGGER-COMPATIBILITY .. rubric:: 兼容性 :name: 兼容性 PostgreSQL中的\ ``CREATE TRIGGER``\ 语句实现了SQL标准的一个子集。目前缺少下列功能： .. container:: itemizedlist - 虽然\ ``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扩展。 .. container:: refsect1 :name: id-1.9.3.93.11 .. rubric:: 参见 :name: 参见 `ALTER TRIGGER `__, `DROP TRIGGER `__, `CREATE FUNCTION `__, `SET CONSTRAINTS `__