一个完整的触发器例子

PostgreSQL 9.4.4 中文手册
上一页 | 上一级 | 章 36. 触发器 | 下一页

# 36.4. 一个完整的触发器例子

这里是一个用C写的非常简单的触发器例子。（用程序语言写的触发器例子可以在程序语言文档中找到）。

函数`trigf`报告`ttest`表的行数量， 并且如果命令试图把NULL插入到字段`x`里 (也就是它做为一个非空约束但不退出事务)时略过操作。

首先，表定义：

    CREATE TABLE ttest (
        x integer
    );

这里是触发器函数的源代码：

    #include "postgres.h"
    #include "executor/spi.h"        /* 你用 SPI 的时候要用的头文件 */
    #include "commands/trigger.h"   /* ... 触发器 ... */
    #include "utils/rel.h"          /* ... 和关系 */

    #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;

        /* 确信自己是作为触发器调用的 */
        if (!CALLED_AS_TRIGGER(fcinfo))
            elog(ERROR, "trigf: not called by trigger manager");

        /* 返回给执行者的行 */
        if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
            rettuple = trigdata->tg_newtuple;
        else
            rettuple = trigdata->tg_trigtuple;

        /* 检查 NULL 值 */
        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;

        /* 与 SPI 管理器连接 */
        if ((ret = SPI_connect()) < 0)
            elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);

        /* 获取表中的行数量 */
        ret = SPI_exec("SELECT count(*) FROM ttest", 0);

        if (ret < 0)
            elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);

        /* count(*) 返回 int8 ，所以要小心转换  */
        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);
    }

编译完源代码后(参见第 35.9.6 节)，声明函数并创建触发器：

    CREATE FUNCTION trigf() RETURNS trigger
        AS 'filename'
        LANGUAGE 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();

现在你可以测试触发器的操作：

    => INSERT INTO ttest VALUES (NULL);
    INFO:  trigf (fired before): there are 0 rows in ttest
    INSERT 0 0

    -- 插入被忽略，AFTER 触发器没有触发

    => 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
                                           ^^^^^^^^
                                 还记得讲过的关于可视性的原则吗？
    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
                                           ^^^^^^
                                 还记得讲过的关于可视性的原则吗？
    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
                                           ^^^^^^
                                 还记得讲过的关于可视性的原则吗？
    DELETE 2
    => SELECT * FROM ttest;
     x
    ---
    (0 rows)

在`src/test/regress/regress.c`和 spi里还有更复杂的例子。

上一页 | 起始页 | 下一页
用C写触发器 | 上一级 | 事件触发器