1. 简介

postgresql 的一个触发器是一种声明，告诉数据库应该在执行特定的操作的时候执行特定的函数。本文我们将通过一个C语言编写的函数来演示触发器的机制。

2. 准备

2.1 安装数据库，初始化并启动

#/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

2.2 建立测试数据库及表

#/usr/local/pgsql/bin/createdb trigger_testdb
#/usr/local/pgsql/bin/psql trigger_testdb

CREATE TABLE ttest (
    x integer
);

2016-01-09_9-39-44

3. 步骤

3.1 函数编译

编写C函数并编译，代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
#include "postgres.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 */
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum trigf(PG_FUNCTION_ARGS);

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;

/* 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 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);
}

用以下命令编译

#gcc -fPIC -I /home/src/pgsql/src/include -shared -o trigger_testc.so trigger_testc.c

把编译好的so文件复制到lib目录下

#cp trigger_testc.so /usr/local/pgsql/lib

3.2 创建触发器

加载编译好的so文件到数据库

load 'trigger_testc'

创建函数

CREATE FUNCTION trigf() RETURNS trigger
    AS 'trigger_testc'
    LANGUAGE C;

创建两个触发器，一个触发在INSERT/UPDATE/DELETE事务之前，一个触发在INSERT/UPDATE/DELETE事务之后。触发后执行trigf函数，这个函数也是我们刚才编译的C语言函数。

CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
    FOR EACH ROW EXECUTE PROCEDURE trigf();
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
    FOR EACH ROW EXECUTE PROCEDURE trigf();

2016-01-09_10-06-43

4. 测试

1) 插入空值

INSERT INTO ttest VALUES (NULL);

2016-01-09_10-09-38

2)插入第一条记录

INSERT INTO ttest VALUES (1);

3)插入第二条记录

INSERT INTO ttest SELECT x * 2 FROM ttest;

2016-01-09_10-11-15

4)更新第二条记录

UPDATE ttest SET x = NULL WHERE x = 2;
UPDATE ttest SET x = 4 WHERE x = 2;

2016-01-09_10-13-10

5)删除记录

DELETE FROM ttest;

2016-01-09_10-14-03

参考官网