本文共 5459 字，大约阅读时间需要 18 分钟。

PostgreSQL 触发器使用指南

触发器概述

触发器是PostgreSQL中一个强大的功能，它允许在特定数据库操作发生时自动执行预定义的函数。这些触发器可以附加到表、视图或外表上，并根据操作类型（INSERT、UPDATE、DELETE）触发相应的函数。

触发器的类型

触发器可以根据触发时间分为以下几种类型：

触发器的定义

在创建触发器之前，需要先定义一个触发器函数。该函数必须满足以下条件：

• 必须是无参函数。
• 必须返回trigger类型。

如何创建触发器

在定义好触发器函数后，可以使用CREATE TRIGGER命令来创建触发器。一个触发器函数可以用于多个触发器。

触发器的执行顺序

如果同一对象上定义了多个触发器，触发器将按照字母顺序执行。

触发器的可见性

触发器函数的可见性遵循以下规则：

• 语句级BEFORE触发器在操作发生前执行，且对触发器函数不可见。
• 行级触发器的可见性取决于触发时间：
  • 行级BEFORE触发器在操作前执行，对触发器函数不可见。
  • 行级AFTER触发器在操作后执行，对触发器函数可见。
  • 行级INSTEAD OF触发器在操作中立即触发，且对触发器函数可见。

数据变更的可见性

在触发器函数中执行SQL命令时，需要注意数据变更的可见性规则：

• 语句级触发器遵循简单可见性规则：语句级BEFORE触发器对触发器函数不可见，而语句级AFTER触发器可见。
• 行级触发器的可见性取决于触发时间：
  • 行级BEFORE触发器在操作前执行，对触发器函数不可见。
  • 行级AFTER触发器在操作后执行，对触发器函数可见。
  • 行级INSTEAD OF触发器在操作中立即触发，且对触发器函数可见。

使用C编写触发器函数

如果你选择使用C语言编写触发器函数，可以按照以下步骤进行：

#include "postgres.h"#include "fmgr.h"#include "executor/spi.h"#include "commands/trigger.h"#include "utils/rel.h"PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(trigf);

Datum trigf(PG_FUNCTION_ARGS) {    TriggerData *trigdata = (TriggerData *)fcinfo->context;    char *when;    bool checknull = false;    if (!CALLED_AS_TRIGGER(fcinfo)) {        elog(ERROR, "trigf: not called by trigger manager");    }    when = TRIGGER_FIRED_BEFORE(trigdata->tg_event) ? "before" : "after ";    TupDesc tupdesc = trigdata->tg_relation->rd_att;    if ((ret = SPI_connect()) < 0) {        elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);    }    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) {        rettuple = trigdata->tg_trigtuple;    } else {        rettuple = trigdata->tg_newtuple;    }    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 ";    }    if ((ret = SPI_exec("SELECT count(*) FROM ttest", 0)) < 0) {        elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);    }    i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], tupdesc, 1, &isnull));    elog(INFO, "trigf (fired %s): there are %d rows in ttest", when, i);    SPI_finish();    if (checknull) {        if (isnull) {            rettuple = NULL;        }    }    return PointerGetDatum(rettuple);}

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

触发器的完整示例

以下是一个简单的C触发器函数示例：

创建表

CREATE TABLE ttest (x integer);

触发器函数源码

#include "postgres.h"#include "fmgr.h"#include "executor/spi.h"#include "commands/trigger.h"#include "utils/rel.h"PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(trigf);Datum trigf(PG_FUNCTION_ARGS) {    TriggerData *trigdata = (TriggerData *)fcinfo->context;    TupleDesc tupdesc;    HeapTuple rettuple;    char *when;    bool checknull = false;    bool isnull;    int ret, i;    if (!CALLED_AS_TRIGGER(fcinfo)) {        elog(ERROR, "trigf: not called by trigger manager");    }    when = TRIGGER_FIRED_BEFORE(trigdata->tg_event) ? "before" : "after ";    tupdesc = trigdata->tg_relation->rd_att;    if ((ret = SPI_connect()) < 0) {        elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);    }    if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) {        rettuple = trigdata->tg_trigtuple;    } else {        rettuple = trigdata->tg_newtuple;    }    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 ";    }    if ((ret = SPI_exec("SELECT count(*) FROM ttest", 0)) < 0) {        elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);    }    i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0], tupdesc, 1, &isnull));    elog(INFO, "trigf (fired %s): there are %d rows in ttest", when, i);    SPI_finish();    if (checknull) {        if (isnull) {            rettuple = NULL;        }    }    return PointerGetDatum(rettuple);}

创建触发器

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

测试触发器

-- INSERT INTO ttest VALUES (NULL);INFO: trigf (fired before): there are 0 rows in ttestINSERT 0 0-- Insertion skipped and AFTER trigger is not fired-- INSERT INTO ttest VALUES (1);INFO: trigf (fired before): there are 0 rows in ttestINFO: trigf (fired after): there are 1 rows in ttest-- INSERT INTO ttest SELECT x * 2 FROM ttest;INFO: trigf (fired before): there are 1 rows in ttestINFO: trigf (fired after): there are 2 rows in ttest-- UPDATE ttest SET x = NULL WHERE x = 2;INFO: trigf (fired before): there are 2 rows in ttestUPDATE 0-- UPDATE ttest SET x = 4 WHERE x = 2;INFO: trigf (fired before): there are 2 rows in ttestINFO: trigf (fired after): there are 2 rows in ttestUPDATE 1vac-- DELETE FROM ttest;INFO: trigf (fired before): there are 2 rows in ttestINFO: trigf (fired before): there are 1 rows in ttestINFO: trigf (fired after): there are 0 rows in ttestINFO: trigf (fired after): there are 0 rows in ttest-- SELECT * FROM ttest;(0 rows)

更多示例和详细文档，请参考PostgreSQL官方文档和相关开发资源。

转载地址：http://bbowz.baihongyu.com/