PG中的触发器

0    563    2

Tags:

👉 本文共约9685个字,系统预计阅读时间或需37分钟。

简介

PostgreSQL 触发器是数据库的回调函数,它会在指定的数据库事件发生时自动执行/调用。

下面是关于 PostgreSQL 触发器几个比较重要的点:

  • PostgreSQL 触发器可以在下面几种情况下触发:
    • 在执行操作之前(在检查约束并尝试插入、更新或删除之前)。
    • 在执行操作之后(在检查约束并插入、更新或删除完成之后)。
    • 更新操作(在对一个视图进行插入、更新、删除时)。
  • 触发器的 FOR EACH ROW 属性是可选的,如果选中,当操作修改时每行调用一次;相反,选中 FOR EACH STATEMENT,不管修改了多少行,每个语句标记的触发器执行一次。
  • WHEN 子句和触发器操作在引用 NEW.column-name 和 OLD.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
  • 如果存在 WHEN 子句,PostgreSQL 语句只会执行 WHEN 子句成立的那一行,如果没有 WHEN 子句,PostgreSQL 语句会在每一行执行。
  • BEFORE 或 AFTER 关键字决定何时执行触发器动作,决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
  • 要修改的表必须存在于同一数据库中,作为触发器被附加的表或视图,且必须只使用 tablename,而不是 database.tablename。
  • 当创建约束触发器时会指定约束选项。这与常规触发器相同,只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时,它将抛出异常。

创建触发器语法

创建触发器时的基础语法如下:

在这里,event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。

以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器的语法:

实例

让我们假设一个情况,我们要为被插入到新创建的 COMPANY 表(如果已经存在,则删除重新创建)中的每一个记录保持审计试验:

为了保持审计试验,我们将创建一个名为 AUDIT 的新表。每当 COMPANY 表中有一个新的记录项时,日志消息将被插入其中:

在这里,ID 是 AUDIT 记录的 ID,EMP_ID 是来自 COMPANY 表的 ID,DATE 将保持 COMPANY 中记录被创建时的时间戳。所以,现在让我们在 COMPANY 表上创建一个触发器,如下所示:

auditlogfunc() 是 PostgreSQL 一个程序,其定义如下:

现在,我们开始往 COMPANY 表中插入数据:

这时,COMPANY 表中插入了一条记录:

同时, AUDIT 表中也插入了一条记录,因为我们在插入 COMPANY 表时创建了一个触发器。相似的,我们也可以根据需求在更新和删除时创建触发器:

CREATE TRIGGER — 定义一个新触发器

大纲

描述

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

该触发器可以被指定为在一行上尝试该操作之前触发(在约束被检查并且INSERTUPDATE或者DELETE被尝试之前);也可以在该操作完成之后触发(在约束被检查并且INSERTUPDATE或者DELETE完成之后);或者取代该操作(在对一个视图插入、更新或删除的情况中)。如果该触发器在事件之前触发或者取代事件,该触发器可以跳过对当前行的操作或者改变正在被插入的行(只对INSERT以及UPDATE操作)。如果该触发器在事件之后触发,所有更改(包括其他触发器的效果)对该触发器“可见”。

一个被标记为FOR EACH ROW的触发器会对该操作修改的每一行都调用一次。例如,一个影响 10 行的DELETE将导致在目标关系上的任何ON DELETE触发器被独立调用 10 次,也就是为每一个被删除的行调用一次。与此相反,一个被标记为FOR EACH STATEMENT的触发器只会为任何给定的操作执行一次,不管该操作修改多少行(特别地,一个修改零行的操作将仍会导致任何可用的FOR EACH STATEMENT触发器被执行)。

被指定为要触发INSTEAD OF触发器事件的触发器必须被标记为FOR EACH ROW,并且只能被定义在视图上。一个视图上的BEFOREAFTER触发器必须被标记为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仅可以被指定一次,并且只能为在UPDATEDELETE事件上引发的触发器指定,它创建的传递关系包含有该语句更新或删除的所有行的前映像。类似地,NEW TABLE仅可以被指定一次,并且只能为在UPDATEINSERT事件上引发的触发器指定,它创建的传递关系包含有该语句更新或插入的所有行的后映像

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

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

参数

  • name

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

  • BEFORE AFTER INSTEAD OF

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

  • event

    INSERTUPDATEDELETE或者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*或者NEW.*column_name*来引用列的新旧行值。当然,INSERT触发器不能引用OLD并且DELETE触发器不能引用NEWINSTEAD OF触发器不支持WHEN条件。当前,WHEN表达式不能包含子查询。注意对于约束触发器,对于WHEN条件的计算不会被延迟,而是直接在行更新操作被执行之后立刻发生。如果该条件计算得不到真,那么该触发器就不会被放在延迟执行的队列中。

  • function_name

    一个用户提供的函数,它被声明为不用参数并且返回类型trigger,当触发器引发时会执行该函数。在CREATE TRIGGER的语法中,关键词FUNCTIONPROCEDURE是等效的,但是任何情况下被引用的函数必须是一个函数而不是过程。这里,关键词PROCEDURE的使用是有历史原因的并且已经被废弃。

  • arguments

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

注解

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

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

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

有一些内置的触发函数可用于解决常见问题,而无需编写您自己的触发代码。请参见第 9.28 节

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

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

在一些情况下,单一的SQL命令可能会引发多种触发器。例如,一个带有ON CONFLICT DO UPDATE子句的INSERT可能同时导致插入和更新操作,因此它将根据需要引发这两种触发器。提供给触发器的传递关系与它们的事件类型有关,因此INSERT触发器将只看到被插入的行,而UPDATE触发器将只看到被更新的行。

由外键强制动作导致的行更新或删除(例如ON UPDATE CASCADEON DELETE SET NULL)被当做导致它们的SQL命令的一部分。受影响的表上的相关触发器将被引发,这样就提供了另一种方法让SQL命令引发不直接匹配其类型的触发器。在简单的情况中,请求传递关系的触发器将在一个传递关系中看到由原始SQL命令在其表中做出的所有改变。不过,有些情况中一个请求传递关系的AFTER ROW触发器的存在将导致由单个SQL命令触发的外键强制动作被分成多步,每一步都有其自己的传递关系。在这种情况下,没创建一个传递关系集合都会引发存在的所有语句级触发器,确保那些触发器能够在一个传递关系中看到每个受影响的行一次,并且只看到一次。

只有当视图上的动作被一个行级INSTEAD OF触发器处理时才会引发视图上的语句级触发器。如果动作被一个INSTEAD规则处理,那么该语句发出的任何语句都会代替提及该视图的原始语句执行,这样将被引发的触发器是替换语句中提及的表上的那些触发器。类似地,如果视图是自动可更新的,则该动作将被处理为把该语句自动重写成在视图基表上的一个动作,这样基表的语句级触发器就是要被引发的。

在分区表上创建一个行级触发器将导致在它所有的现有分区上创建相同的触发器,并且以后创建或者挂接的任何分区也将包含一个相同的触发器。 如果该分区与其父分区分离,则触发器将被删除。分区表上的触发器只能是AFTER

修改分区表或者带有继承子表的表会引发挂接到显式提及表的语句级触发器,但不会引发其分区或子表的语句级触发器。相反,行级触发器会在受影响的分区或子表上引发,即便它们在查询中没有被明确提及。如果一个语句级触发器用REFERENCING子句定义有传递关系,则来自所有受影响分区或子表中的行的前后映像都是可见的。在继承子表的情况中,行映像仅包括该触发器所附属的表中存在的列。当前,不能在分区或继承子表上定义带有传递关系的行级触发器。

例子

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

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

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

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

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

为每个语句执行函数check_transfer_balances_to_zero以确认transfer的行不会有净值增加:

为每一行执行函数check_matching_pairs以确认(同一个语句)同时对匹配对做了更改 :

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

兼容性

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

  • 虽然AFTER触发器的传递表名是以标准的方式用REFERENCING子句指定,但REFERENCING子句中不能指定FOR EACH ROW触发器中用到的行变量。它们以依赖于编写该触发器函数的语言的方式可用,但是对任意一种语言来说是固定的。一些语言实际上的行为就像有包含OLD ROW AS OLD NEW ROW AS NEWREFERENCING子句存在一样。
  • 标准允许把传递表与和列相关的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扩展。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

列出触发器

你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来:

如果,你想列举出特定表的触发器,语法如下:

得到结果如下:

删除触发器(DROP TRIGGER)

删除触发器基础语法如下:

删除本文上表 company 上的触发器 example_trigger 的指令为:

大纲

描述

DROP TRIGGER移除一个现有的触发器定义。 要执行这个命令,当前用户必须是触发器基表的拥有者。

参数

  • IF EXISTS

    如果该触发器不存在则不要抛出一个错误,而是发出一个提示。

  • name

    要移除的触发器的名称。

  • table_name

    定义了该触发器的表的名称(可以是模式限定的)。

  • CASCADE

    自动删除依赖于该触发器的对象,然后删除所有 依赖于那些对象的对象(见第 5.14 节)。

  • RESTRICT

    如果有任何对象依赖于该触发器,则拒绝删除它。这是默认值。

示例

销毁表films上的触发器 if_dist_exists

兼容性

PostgreSQL中的 DROP TRIGGER语句与 SQL 标准不 兼容。在 SQL 标准中,不同表上也不能有同名的触发器,因此其 命令是简单的DROP TRIGGER *name*.

ALTER TRIGGER

ALTER TRIGGER — 更改一个触发器的定义

大纲

描述

ALTER TRIGGER更改一个现有触发器的属性。 RENAME子句更改给定触发器的名称而不更改其定义。 DEPENDS ON EXTENSION子句把该触发器标记为依赖于 一个扩展,这样如果扩展被删除,该触发器也会被自动删除。

要更改一个触发器的属性,你必须拥有该触发器所作用的表。

参数

  • name

    要修改的一个现有触发器的名称。

  • table_name

    这个触发器所作用的表的名称。

  • new_name

    该触发器的新名称。

  • extension_name

    触发器依赖于某名称的扩展(如果指定了NO,则不再依赖)。 删除扩展时,会自动删除标记为依赖于扩展的触发器。

注解

临时启用或者禁用一个触发器的功能由ALTER TABLE而不是 ALTER TRIGGER提供,因为ALTER TRIGGER 无法表示一次性启用或者禁用一个表上所有触发器的选项。

示例

要重命名一个现有的触发器:

要把一个触发器标记为依赖于一个扩展:

兼容性

ALTER TRIGGER是一种 PostgreSQL的 SQL 标准扩展。

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

19 − 1 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部