下面通过具体的例子来说明在postgresql中触发器的建立和使用(老规矩先写代码然后讲解)
#include <postgres.h>
#include <executor/spi.h>
#include <funcapi.h>
#include <commands/trigger.h>
#include <fmgr.h>
extern Datum pg_trigf(PG_FUNCTION_ARGS);
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(pg_trigf);
Datum
pg_trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo->context;
HeapTuple rettuple = NULL;
int ret;
int proc; /* to store the value of SPI_processed (actual row number)*/
/* to be sure this function will be called by trigger */
if (!(CALLED_AS_TRIGGER(fcinfo))) {
elog(ERROR, "trigf: not called by trigger manager");
}
/* should be fired by statement */
if (TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) {
elog(ERROR, "cannot process row events");
}
/* should be fired before event */
if (TRIGGER_FIRED_AFTER(trigdata->tg_event)) {
elog(ERROR, "must be fired before event");
}
/* connect spi manager */
if ((ret = SPI_connect()) < 0) {
elog(INFO, "SPI_connect failed: SPI_connect returned: %d", ret);
return PointerGetDatum(rettuple);
}
/* check the permanent table name(perm_user) exists or not*/
ret = SPI_exec("SELECT tablename FROM pg_tables WHERE tablename LIKE 'perm!_user' ESCAPE '!';", 1);
proc = SPI_processed;
if (ret != SPI_OK_SELECT) {
elog(INFO, "SPI_exec execute error: user table.");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* create the permanent table(perm_user) if it does not exist */
if (proc < 1) {
/* create permanent table: perm_user */
ret = SPI_exec("CREATE TABLE perm_user AS SELECT * FROM tbl_user;", 0);
if (ret != SPI_OK_SELINTO ) {
elog(INFO, "SPI_exec execute error: fail to create perm_user");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* set attribute to perm_user */
ret = SPI_exec("ALTER TABLE perm_user ADD PRIMARY KEY (user_name);", 0);
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_exec execute error: fail to add primary key to perm_user");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* set attribute to perm_user*/
ret = SPI_exec("ALTER TABLE perm_user ALTER user_passwd SET NOT NULL;", 0);
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_exec execute error: fail to set attribute to password.");
SPI_finish();
return PointerGetDatum(rettuple);
}
}
.....
/* check the permanent table name(perm_member) exists or not */
ret = SPI_exec("SELECT tablename FROM pg_tables WHERE tablename LIKE 'perm!_member' ESCAPE '!';", 1);
proc = SPI_processed;
if (ret != SPI_OK_SELECT) {
elog(INFO, "SPI_exec execute error tbl_member");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* create the permanent table(perm_member) if it does not exist */
if (proc < 1) {
/* create permanent table: perm_member */
ret = SPI_exec("CREATE TABLE perm_member AS SELECT * FROM tbl_member;", 0);
if (ret != SPI_OK_SELINTO) {
elog(INFO, "SPI_exec execute error");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* set attribute to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADD CONSTRAINT user_fk FOREIGN KEY (user_name) REFERENCES perm_user(user_name) ON DELETE CASCADE ON UPDATE CASCADE;", 0);
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_exec execute error: fail to set attribute to user_name.");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* set attribute to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADD CONSTRAINT group_fk FOREIGN KEY (grp_name) REFERENCES perm_group(grp_name) ON DELETE CASCADE ON UPDATE CASCADE;", 0);
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_exec execute error: fail to set attribute to grp_name.");
SPI_finish();
return PointerGetDatum(rettuple);
}
/* add primary key to perm_member */
ret = SPI_exec("ALTER TABLE perm_member ADD PRIMARY KEY (user_name, grp_name);", 0);
if (ret != SPI_OK_UTILITY) {
elog(INFO, "SPI_exec execute error: fail to add primary key to perm_member.");
SPI_finish();
return PointerGetDatum(rettuple);
}
}
/*close connect with SPI manager */
SPI_finish();
/* return back must be NULL*/
return PointerGetDatum(rettuple);
}
这个函数写法与postgresql服务端函数的写法很相似, 但是不完全相同.具体需要注意的地方是:
1. 需要多添加头文件:#include <commands/trigger.h>
2. 这个函数的返回值一定是trigger类型的.
3. 函数的开始最好确认我们这个函数是供触发器调用的并且明确一下自己要写的触发器的类型是什么,然后做一下判断,以免别的语句也触发我们的触发器.
二. 接下来的事情是编译:
gcc -fpic -c trigger.c -I/usr/local/postgreSQL/include/postgresql/server
gcc -shared -o trigger.so trigger.o
如果不明白可以参考手册(说句题外话,手册的作用实在是太大了,在手册中也提供了一例子).
三. 在数据库中创建函数和触发器:
/* create a trigger used to write memory and config memory */
CREATE OR REPLACE FUNCTION pg_trigf() RETURNS trigger
AS 'filename'
LANGUAGE C IMMUTABLE STRICT;
CREATE TRIGGER tbuser BEFORE INSERT OR UPDATE OR DELETE
ON tbl_user FOR EACH STATEMENT
EXECUTE PROCEDURE pg_trigf();
CREATE TRIGGER tbgroup BEFORE INSERT OR UPDATE OR DELETE
我创建的触发器是语句触发器,这个和手册上的不一样, 手册上的是行触发器.
然后在数据库中使用SQL语句就可以看到触发器的效果了.
哈哈... 大功终于告成......
我上面写的代码是测试过了的,可以使用.
如果你有什么问题,我很希望你能来和我讨论, 这样我们就可以共同进步.
如果你要转载我的这篇文章,麻烦你注明出处.(因为这个花了我不少的心血,为了写个触发器,我花了好几天啊.....).