作者:瀚高PG实验室(Highgo PG Lab) 丹心明月

注:本文章主要翻译自《PostgreSQL 13.0 Documentation》第三十八章

本章介绍编写函数触发器的相关信息。触发器函数可使用大部分可用的过程语言编写,包括PL/pgSQL(参见第42章),PL/Tcl(参见第43章),PL/Perl(参见第44章),和PL/Python(参见第45章)。阅读完本章后,可根据自己的喜好,到相应章节查看对应语言编写触发器的具体细节。


也可以使用C语言编写触发器,不过使用过程语言更简单一些。当前,还无法单纯的使用SQL函数语言编写触发器函数。

38.1 触发器概览

触发器定义了,在发生特定的操作的时候,数据库应自动执行的函数。触发器可创建到表(分区或非分区均可)、视图及外表上(使用CREATE TRIGGER创建触发器)。


在表和外部表上,触发器可定义在INSERT,UPDATE或DELETE之前|之后,针对每行或每个SQL语句执行。UPDATE触发器可针对某一列设置。TRUNCATE也可触发触发器。一旦触发器事件发生,触发器函数就会在合适的时间调用以处理该事件。


在视图上,触发器可定义为INSERT,UPDATE或DELETE操作的替代执行。此类INSERT OF触发器针对视图中更新的每一行触发。由触发器函数对视图的基表数据进行处理。视图上的触发器也可定义为在INSERT,UPDATE或DELETE之前|之后,针对每个SQL语句触发。不过,此类触发器仅当在此视图上也定义了INSTEAD OF触发器时才会触发。


触发器函数需要在创建触发器之前创建。触发器函数必须声明为无参,且返回trigger类型。


在创建触发器函数之后,使用CREATE TRIGGER命令创建触发器。一个触发器函数可用于多个触发器。


PostgreSQL提供行级触发器和语句级触发器。行级触发器,对于每个影响的行均触发一次;语句级触发器,不管语句影响多少行,均针对语句触发一次。特别注意,符合触发条件的语句,即使未影响任何行,依旧会触发语句级触发器。TRUNCATE的触发器只可定义为语句级触发器。


触发器还可据触发时间进行分类:before,after或instead of。分别称为BEFORE触发器、AFTER触发器和INSTEAD OF触发器。语句级BEFORE触发器在语句开始前触发,语句级AFTER触发器在语句执行后触发。这两种类型的触发器可定义在表、视图或外表上。行级BEFORE触发器在对匹配行操作前触发,而行级AFTER触发器在语句结束时触发(但在语句级AFTER触发器前触发)。此类触发器仅可定义在表和外表上,而不可定义在视图上。INSTEAD OF触发器仅可定义在视图上,且仅可为行级;此类触发器在对视图中匹配行进行处理时立即触发。


对继承中,对父表的操作,不会触发在自表上定义的语句级触发器;仅会触发附表上的语句级触发器。不过,会触发行级触发器。如果对分区表的UPDATE操作导致行跨分区移动,那么表现为原分区的DELETE操作和对新分区的INSERT操作。因此会触发行级BEFORE UPDATE、行级BEFORE DELETE、行级BEFORE INSERT、行级AFTER DELETE和AFTER INSERT触发器。而对于语句级触发器,仅会触发UPDATE语句中涉及的目标表上定义的触发器。


语句触发器调用的触发器函数应始终返回NULL;行级触发器调用的触发器函数可返回表行(HealTuple类型的值)。before行级触发器可有以下选择:

  • 返回NULL,以跳过对当前行的操作。

  • 对于行级INSERT和UPDATE触发器,可对更新或插入的行进行处理;


行级INSTEAD OF触发器,如果未更新基表的数据,则应返回NULL;否则,返回操作的行数。


After行级触发器的返回值可忽略,故可返回NULL。


如果在相同对象上定义了对相同事件的多个触发器,则按照字母顺序执行这些触发器。


也可通过指定WHEN布尔条件,以定义触发器是否该被触发。


行级触发器中,使用NEW指代INSERT或UPDATE中的输入数据;使用OLD指代UPDATE和DELETE的旧数据。

38.2 数据变更的可见性

如果在触发器函数中执行SQL命令,且这些命令会访问触发器所在表,则需要知晓数据可见性规则,因为它们决定了SQL命令是否可看到触发触发器的数据变化。它们是:

  • 语句级触发器遵循简单可见性规则:语句所做的更改对语句级BEFORE触发器均不可见;不过对语句级AFTER触发器可见。

  • 触发触发器的数据变更(增、删、改)一般对在语句级BEFORE触发器中执行的SQL命令不可见,因为那时变更还未发生。

  • 不过,在行级BEFORE触发器中执行的SQL命令可以看到由同一外部命令处理的数据变更。

  • 同样,一个行级INSTEAD OF触发器可看到在同一外部命令中之前执行的INSTEAD OF触发器所造成的数据变更。

  • 当行级AFTER触发器触发时,所有外部命令所做的数据变更均已完成,故而对触发器函数可见。


如果使用标准的过程语言编写触发器函数,则仅当函数声明为VOLATILE时遵循以上规则。所有变化对声明为STABLE或IMMUTABLE的函数不可见。

38.3 使用C编写触发器函数

本节介绍使用C编写触发器函数的相关信息。触发器函数必须使用“version 1”函数管理界面。


当触发器管理者调用函数时,传递的不是普通的参数,而是一个执行TriggerData架构的额上下文指针。C语言通过执行以下宏来确定是否被触发器管理者调用:

CALLED_AS_TRIGGER(fcinfo)

扩展为:

((fcinfo)->context != NULL && IsA((fcinfo)->context, TriggerData))

如果返回true,则将fcinfo->context转换为TriggerData *是安全的,指针指向TriggerData架构。函数不可更改TriggerData架构或它指向的任何数据。


struct TriggerData在命令/trigger.h中定义。

typedef struct TriggerData{NodeTag type;
TriggerEvent tg_event;
Relation tg_relation;
HeapTuple tg_trigtuple;
HeapTuple tg_newtuple;
Trigger *tg_trigger;
TupleTableSlot *tg_trigslot;
TupleTableSlot *tg_newslot;
Tuplestorestate *tg_oldtable;
Tuplestorestate *tg_newtable;const Bitmapset *tg_updatedcols;
} TriggerData;

组成解释:

type

    始终为T_TriggerData。

tg_event

    函数触发事件。可使用如下宏测试tg_event:

        TRIGGER_FIRED_BEFORE(tg_event):若为before,则返回true。

        TRIGGER_FIRED_AFTER(tg_event):若为after,返回true。

        TRIGGER_FIRED_INSTEAD(tg_event):若为instead of,返回true。

        TRIGGER_FIRED_FOR_ROW(tg_event):若为行级事件,则返回true。

        TRIGGER_FIRED_FOR_STATEMENT(tg_event):若为语句级事件,则返回true。

        TRIGGER_FIRED_BY_INSERT(tg_event):若由INSERT触发,则返回true。

        TRIGGER_FIRED_BY_UPDATE(tg_event)

        TRIGGER_FIRED_BY_DELETE(tg_event)

        TRIGGER_FIRED_BY_TRUNCATE(tg_event)

tg_relation

    指向触发器触发的对象。

tg_trigtuple

    指向触发器触发行的指针。

tg_newtuple

    触发行的新版本。

tg_trigger

    指向定义在utils/reltrigger.h中Trigger类型架构的指针。

typedef struct Trigger{Oid tgoid;char *tgname;  --触发器名称
Oid tgfoid;
int16 tgtype;char tgenabled;bool tgisinternal;
Oid tgconstrrelid;
Oid tgconstrindid;
Oid tgconstraint;bool tgdeferrable;bool tginitdeferred;
int16 tgnargs;  --tgargs中参数个数
int16 tgnattr;
int16 *tgattr;char **tgargs;  --指向CREATE TRIGGER语句中定义的参数指针数组char *tgqual;char *tgoldtable;char *tgnewtable;
} Trigger;

tg_trigslot

    tg_trigtuple中的槽,没有的话返回NULL。

tg_newslot

    tg_newtuple中的槽,没有的话返回NULL。

tg_oldtable

    一个指向Tuplestorestate类型的架构,其中包含由tg_relation定义的0或多行;若无,返回NULL。

tg_newtabe

    一个指向Tuplestorestate类型的架构,其中包含由tg_relation定义的0或多行;若无,返回NULL。

tg_updatedcols

    UPDATE涉及的列。


触发器函数必须返回HeapTuple指针或空指针。如果不想更新操作行,返回tg_trigtuple或tg_newtuple时,请谨慎。

38.4 触发器完整示例

以下为一个简单的C触发器函数示例。函数trigf返回表ttest的行数,且忽略插入列x中的值为null的操作(即类似于非空约束,但不中断事务)。


首先,创建表:

CREATE TABLE ttest (
x integer);

触发器函数源码:

#include "postgres.h"#include "fmgr.h"#include "executor/spi.h" /* this is what you need to work with SPI */#include "commands/trigger.h" /* ... triggers ... */#include "utils/rel.h" /* ... and relations */PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(trigf);Datumtrigf(PG_FUNCTION_ARGS){
       TriggerData* trigdata = (TriggerData*)fcinfo->context;
       TupleDesc tupdesc;
       HeapTuple rettuple;       char* when;       bool checknull = false;       bool isnull;       int ret, i;       /* make sure it's called as a trigger at all */
       if (!CALLED_AS_TRIGGER(fcinfo))
              elog(ERROR, "trigf: not called by trigger manager");       /* tuple to return to executor */
       if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
              rettuple = trigdata->tg_newtuple;       else
              rettuple = trigdata->tg_trigtuple;       /* check for null values */
       if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
              && TRIGGER_FIRED_BEFORE(trigdata->tg_event))
              checknull = true;       if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
              when = "before";       else
              when = "after ";
       tupdesc = trigdata->tg_relation->rd_att;       /* connect to SPI manager */
       if ((ret = SPI_connect()) < 0)
              elog(ERROR, "trigf (fired %s): SPI_connect returned %d",
                      when, ret);       /* get number of rows in table */
       ret = SPI_exec("SELECT count(*) FROM ttest", 0);       if (ret < 0)
              elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when,
                      ret);       /* count(*) returns int8, so be careful to convert */
       i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
              SPI_tuptable->tupdesc,              1,
              &isnull));
       elog(INFO, "trigf (fired %s): there are %d rows in ttest",
              when, i);
       SPI_finish();       if (checknull)
       {
              SPI_getbinval(rettuple, tupdesc, 1, &isnull);              if (isnull)
                      rettuple = NULL;
       }       return PointerGetDatum(rettuple);
}

接下来,声明函数和触发器:

CREATE FUNCTION trigf() RETURNS triggerAS 'filename'LANGUAGE C;CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttestFOR EACH ROW EXECUTE FUNCTION trigf();CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttestFOR EACH ROW EXECUTE FUNCTION trigf();

测试触发器:

=> INSERT INTO ttest VALUES (NULL);
INFO: trigf (fired before): there are 0 rows in ttest
INSERT 0 0
-- Insertion skipped and AFTER trigger is not fired
=> SELECT * FROM ttest;
x
---
(0 rows)
=> INSERT INTO ttest VALUES (1);
INFO: trigf (fired before): there are 0 rows in ttest
INFO: trigf (fired after ): there are 1 rows in ttest
^^^^^^^^
remember what we said about
visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
---
1
(1 row)
=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
^^^^^^
remember what we said about
visibility.
INSERT 167794 1
=> SELECT * FROM ttest;
x
---
1
2
(2 rows)
=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
x
---
1
4
(2 rows)
=> DELETE FROM ttest;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
^^^^^^
remember what we said about
visibility.
DELETE 2
=> SELECT * FROM ttest;
x
---
(0 rows)

更多示例,可参见src/test/regress/regress.c以及spi