postgresql 触发器简介（转）

以Postgresql 9.2为例,介绍触发器的使用. 分两部分.
包含如下内容 : 
一、
1. 什么是触发器? 触发器有什么用? 创建触发器的语法?
2. 什么是触发器函数,触发器函数可以用哪些语言编写?
3. 触发器有哪些分类?
4. 同一个表或视图上可以建多少个触发器? 如果一个表或视图上有多个触发器,调用顺序如何决定?
5. 同一个触发器函数可以被多个触发器调用吗? 触发器函数的返回类型时什么? 触发器函数的返回值是否会影响下一个触发器函数或者被操作的行的数据? NEW 或者OLD record修改后会带来什么影响? 哪些触发器函数的返回值没有意义?
6. 触发器函数的返回值与返回行数的关系,与变量FOUND,ROW_COUNT,RETURNING的关系.
7. 触发器的延时属性和状态.
8. 可以在系统表或系统视图上创建触发器吗?
二、
以plpgsql语言为例,讲解触发器函数.
9. 触发器函数的返回值.
10. 触发器函数的参数.
11. 触发器函数中的变量与传递.
12. 触发器函数的数据可视与什么有关?
13. 触发器会无限递归吗? 如何避免?
14. 触发条件与性能.
15. 加入触发器后的事务特性.
16. 触发器的返回值是如何影响returning的结果的?

【正文】
一、
1. 什么是触发器? 触发器有什么用? 创建触发器的语法?
定义还是用原文比较好 : 
A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. 
Triggers can be attached to both tables and views. 
创建触发器的语法如下 : 
Command:     CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [,... ] ]
    DELETE
    TRUNCATE

2. 什么是触发器函数? 触发器函数可以用哪些语言编写?
给表或者视图创建触发器时需要指定这个触发器被触发时执行的函数,这个函数就是触发器函数.
触发器函数的返回类型为trigger,如果需要给触发器函数传入参数,不能定义在触发器函数的参数列表中,而是通过其他方式传入(TriggerData数据结构).
例如使用plpgsql写的触发器函数,通过变量TG_ARGV[]来接收传入的变量值. 
The trigger function must be defined before the trigger itself can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure,not in the form of ordinary function arguments.)

TriggerData数据结构 : 
00029 typedef struct TriggerData
00030 {
00031     NodeTag     type;
00032     TriggerEvent tg_event;
00033     Relation    tg_relation;
00034     HeapTuple   tg_trigtuple;
00035     HeapTuple   tg_newtuple;
00036     Trigger    *tg_trigger;
00037     Buffer      tg_trigtuplebuf;
00038     Buffer      tg_newtuplebuf;
00039 } TriggerData;
Trigger数据结构,传入的参数数据结构 : 
00023 typedef struct Trigger
00024 {
00025     Oid         tgoid;          /* OID of trigger (pg_trigger row) */
00026     /* Remaining fields are copied from pg_trigger,see pg_trigger.h */
00027     char       *tgname;
00028     Oid         tgfoid;
00029     int16       tgtype;
00030     char        tgenabled;
00031     bool        tgisinternal;
00032     Oid         tgconstrrelid;
00033     Oid         tgconstrindid;
00034     Oid         tgconstraint;
00035     bool        tgdeferrable;
00036     bool        tginitdeferred;
00037     int16       tgnargs;
00038     int16       tgnattr;
00039     int16      *tgattr;
00040     char      **tgargs;
00041     char       *tgqual;
00042 } Trigger;
触发器函数可以使用系统自带的过程语言(例如plpgsql,pltcl,plperl,plpython)来写,也可以使用C来写.
后面的实例将使用plpgsql来写.

3. 触发器有哪些分类?
-- 表
-- On tables,triggers can be defined to execute either before or after any INSERT,UPDATE,or DELETE operation,either once per modified row,or once per sql statement. 
UPDATE triggers can moreover be set to fire only if certain columns are mentioned in the SET clause of the UPDATE statement. 
Triggers can also fire for TRUNCATE statements. 
If a trigger event occurs,the trigger's function is called at the appropriate time to handle the event. 表触发器的创建分类 : (before | after) (INSERT | UPDATE | DELETE) (FOR EACH ROW) (WHEN NEW.? and,or OLD.? ... other boolean express ....) (before | after) (INSERT | UPDATE | DELETE | TRUNCATE) (FOR EACH STATEMENT) (WHEN BOOLEAN express except NEW or OLD or columns) -- 视图 -- On views,triggers can be defined to execute instead of INSERT,or DELETE operations. INSTEAD OF triggers are fired once for each row that needs to be modified in the view. It is the responsibility of the trigger's function to perform the necessary modifications to the underlying base tables and,where appropriate,return the modified row as it will appear in the view. 
Triggers on views can also be defined to execute once per sql statement,before or after INSERT,or DELETE operations.
视图触发器的创建分类 : 
(INSTEAD OF) (INSERT | UPDATE | DELETE) (FOR EACH ROW) (WHEN NEW.? and,or OLD.? ... other boolean express ....)
(before | after) (INSERT | UPDATE | DELETE) (FOR EACH STATEMENT) (WHEN BOOLEAN express except NEW or OLD or columns)
分类总结如下 : 
When    Event   Row-level   Statement-level
BEFORE  INSERT/UPDATE/DELETE    Tables  Tables and views
TRUNCATE    — Tables
AFTER   INSERT/UPDATE/DELETE    Tables  Tables and views
TRUNCATE    — Tables
INSTEAD OF  INSERT/UPDATE/DELETE    Views   —
TRUNCATE    — —

4. 同一个表或视图上可以建多个触发器吗? 如果一个表或视图上有多个触发器,调用顺序如何决定?
同一个表或视图上可以创建多个触发器,调用的顺序和触发器的类型有关. 
-- 表上各种触发器的调用先后顺序如下 : 
1. before for each statement
2. before for each row
3. after for each row
4. after for each statement
-- 视图上各种触发器的调用先后顺序如下 : 
1. before for each statement
2. instead for each row
3. after for each statement
-- 同类触发器如果有多个,调用顺序则和触发器的名字有关,按照名字的排序进行调用.
举例,使用raise notice TG_NAME跟踪调用顺序 : 
-- 表举例
-- 创建测试表
postgres=# create table digoal (id int);
CREATE TABLE
-- 创建触发器函数
postgres=# create or replace function debug() returns trigger as $$
declare 
begin  
  raise notice '%',TG_NAME;
  return new;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 创建4种类型的触发器
postgres=# create trigger tg1 before insert on digoal for each statement execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg2 before insert on digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg3 after insert on digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg4 after insert on digoal for each statement execute procedure debug();
CREATE TRIGGER
-- 创建多个同类型的触发器
postgres=# create trigger tg01 before insert on digoal for each statement execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg02 before insert on digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg03 after insert on digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg04 after insert on digoal for each statement execute procedure debug();
CREATE TRIGGER
-- 插入测试 : 
postgres=# insert into digoal values (1);
NOTICE:  00000: tg01  -- 第1被触发的是tg01,before for each statement,同类触发器tg01按字母顺序排在tg1的前面.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg1  -- 第2被触发的是tg1,before for each statement
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg02  -- 第3被触发的是tg02,before for each row,同类触发器tg02按字母顺序排在tg2的前面.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg2  -- 第4被触发的是tg2,before for each row
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg03  -- 第5被触发的是tg03,after for each row,同类触发器tg03按字母顺序排在tg3的前面.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg3  -- 第6被触发的是tg3,after for each row
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg04  -- 第7被触发的是tg04,after for each statement,同类触发器tg04按字母顺序排在tg4的前面.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg4  -- 第8被触发的是tg4,after for each statement
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 1
-- 视图举例
-- 创建测试视图
postgres=# create view v_digoal as select * from digoal;
CREATE VIEW
-- 创建触发器
postgres=# create trigger tg1 before insert on v_digoal for each statement execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg2 instead of insert on v_digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg3 after insert on v_digoal for each statement execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg01 before insert on v_digoal for each statement execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg02 instead of insert on v_digoal for each row execute procedure debug();
CREATE TRIGGER
postgres=# create trigger tg03 after insert on v_digoal for each statement execute procedure debug();
CREATE TRIGGER
-- 测试
postgres=# insert into v_digoal values (2);
NOTICE:  00000: tg01  -- 第1被触发的是tg01,instead of for each row,instead of for each row
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 1

5. 同一个触发器函数可以被多个触发器调用吗? 触发器函数的返回类型是什么? 触发器函数的返回值是否会影响下一个触发器函数或者被操作的行的数据? NEW 或者OLD record修改后会带来什么影响? 哪些触发器函数的返回值没有意义?
-- 同一个触发器函数可以多次被触发器调用,上面的例子中表和视图的14个触发器中都是调用的debug()触发器函数.
-- 触发器函数的返回值为空 或者是 表或视图对应的record类型.
返回值举例 : 
-- 表触发器返回空测试
-- 创建测试表
postgres=# create table t_ret (id int,info text,crt_time timestamp);
CREATE TABLE
-- 创建触发器函数,返回空.
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare
begin
  raise notice '%',TG_NAME; 
  return null;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 创建触发器
postgres=# create trigger tg1 before insert on t_ret for each statement execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg2 before insert on t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg3 after insert on t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg4 after insert on t_ret for each statement execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg01 before insert on t_ret for each statement execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg02 before insert on t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg03 after insert on t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg04 after insert on t_ret for each statement execute procedure tg_t_ret();
CREATE TRIGGER
-- 插入测试
postgres=# insert into t_ret values(1,'digoal',now());
NOTICE:  00000: tg01,before for each statement的触发器函数返回空,不影响后续的触发器是否被调用.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg1
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg02,注意在第一个before for each row触发器调用的触发器函数返回空后,后续的for each row触发器将不被调用. 说明这个返回值传递给了下一个for each row触发器. 并且对后续的for each row触发器造成了影响.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
-- 对表的操作发生在在最后一个before for each row触发器 和 第一个after for each row触发器之间.
-- 因为前面的for each row触发器返回了空,所以没有真正操作的行,因此不会有记录被插入到表里面.
-- 也就是没有发生表记录的操作.
NOTICE:  00000: tg04,after for each statement的触发器函数返回空,pl_exec.c:2840
NOTICE:  00000: tg4
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 0
-- 因为before for each row返回空,所以无值插入
postgres=# select * from t_ret ;
 id | info | crt_time 
----+------+----------
(0 rows)
-- 这个将影响plpgsql函数中FOUND和ROW_COUNT变量的值.

-- 把before for each row的触发器删掉,再测试插入 : 
postgres=# drop trigger tg02 on t_ret;
DROP TRIGGER
postgres=# drop trigger tg2 on t_ret;
DROP TRIGGER
postgres=# insert into t_ret values(1,now());
NOTICE:  00000: tg01
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg03,after for each row 的触发器函数返回空,不影响后续的触发器是否被调用.
因为只要表上面发生了真正的行操作,after for each row就会被触发,除非when条件不满足. (这个后面会讲到)
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg3
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg04
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 1
-- 有数据插入. 这也说明了before for each statement的返回值为空并不会影响数据库对行的操作. 只有before for each row的返回值会影响数据库对行的操作.
postgres=# select * from t_ret ;
 id |  info  |          crt_time          
----+--------+----------------------------
  1 | digoal | 2013-03-10 16:50:39.551481
(1 row)

-- 针对上例,对tg03触发器的触发加一个when条件.
postgres=# drop trigger tg03 on t_ret;
DROP TRIGGER
-- 只有当表的行数据真正受到影响时,after for each row 触发器才会被触发,加了when条件后,则还需要判断这个条件.
postgres=# create trigger tg03 after insert on t_ret for each row when (new.info is not null) execute procedure tg_t_ret();
CREATE TRIGGER
-- 因为new.info 为空,不满足WHEN条件,所以tg03没有被触发.
postgres=# insert into t_ret values(1,null,pl_exec.c:2840
INSERT 0 1
-- 表触发器返回record测试,NEW 或者OLD record修改后会带来什么影响?

-- 前面的例子已经知道,for each statement的返回值对行的值以及for each row的触发器没有影响. 所以下面的例子都是针对for each row的.
INSERT before for each row 触发器函数 接收 NEW,修改这个record对结果的影响是什么?
-- 创建测试表
postgres=# drop table t_ret ;
DROP TABLE
postgres=# create table t_ret(id int,修改NEW并返回.
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare
begin
  NEW.id := NEW.id+1; 
  raise notice '%,id:%',TG_NAME,NEW.id; 
  -- 修改NEW.id,并返回修改后的NEW. 影响插入数据的并不是NEW变量本身,而是return的值,这个在后面将会有例子举证.
  return NEW;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 创建触发器
postgres=# create trigger tg1 before insert ON t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
-- 注意INSERT语句中values提供的id=1,但是注意插入到表里的ID是2. 因此before for each row的返回值对插入行这个动作造成了影响.
postgres=# insert into t_ret values (1,now());
NOTICE:  00000: tg1,id:2
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 1
-- 插入的值被最后一个before for each row触发器函数的返回值取代,篡改为2了:
postgres=# select * from t_ret;
 id |  info  |          crt_time          
----+--------+----------------------------
  2 | digoal | 2013-03-10 17:32:50.648323
(1 row)

-- 用这个触发器函数,更能看出多个触发器函数以及对行数据操作时使用的是触发器函数的返回值,而不是NEW变量本身.
postgres=# \d t_ret
                Table "public.t_ret"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | 
 info     | text                        | 
 crt_time | timestamp without time zone | 
Triggers:
    tg01 BEFORE INSERT ON t_ret FOR EACH ROW EXECUTE PROCEDURE tg_t_ret()
    tg02 AFTER INSERT ON t_ret FOR EACH ROW EXECUTE PROCEDURE tg_t_ret()
    tg1 BEFORE INSERT ON t_ret FOR EACH ROW EXECUTE PROCEDURE tg_t_ret()
    tg2 AFTER INSERT ON t_ret FOR EACH ROW EXECUTE PROCEDURE tg_t_ret()-- 触发器函数如下 : 
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare 
  -- r用来记录原始的NEW值,并返回原始的NEW值.
  r record;
begin
  -- 将NEW赋予给r.
  r := NEW; 
  NEW.id := NEW.id+1; 
  raise notice '%,并返回修改前的NEW . 
  return r;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 插入测试数据
postgres=# insert into t_ret values (100,id:101
LOCATION:  exec_stmt_raise,pl_exec.c:2840
-- 第1个before for each row 的触发器函数中原始NEW.id=100(来自sql语句的输入),返回值r.id=100. 但是NEW.id=101.
NOTICE:  00000: tg1,pl_exec.c:2840
-- 第2个before for each row 的触发器函数中原始NEW.id=100(来自上一个for each row函数的返回值),返回值r.id=100. 但是NEW.id=101.
-- 原始的NEW值没有因为第一个触发器函数的修改而改变,这个触发器函数中的NEW继承了上一个before for each row触发器函数的返回值.
-- 真正写入行的数据,则是最后一个before for each row的返回值. 注意不是NEW变量的值. 而是最终的返回值.
NOTICE:  00000: tg02,pl_exec.c:2840
NOTICE:  00000: tg2,pl_exec.c:2840
-- after for each row 的触发器函数中NEW值(统一来自真正被影响的行数据),与前一个after for each row触发器的返回值无关. (下例举证)
-- 当然这个NEW值也可以理解为(最后一个before for each row的返回值).
INSERT 0 1
postgres=# select * from t_ret ;
 id  |  info  |          crt_time          
-----+--------+----------------------------
 100 | digoal | 2013-03-10 17:50:04.680794
(1 row)

-- 使用这个函数更能观察出after for each row触发器函数的返回值并不影响下一个after for each row的NEW变量.
-- 因为after for each row 的触发器函数中NEW值(统一来自真正被影响的行数据)
-- 修改触发器函数 : 
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare r record;
begin
  NEW.id := NEW.id+1; 
  raise notice '%,并返回修改后的NEW . 
  return NEW;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 插入测试数据
postgres=# insert into t_ret values (123,id:124
LOCATION:  exec_stmt_raise,pl_exec.c:2840
NOTICE:  00000: tg1,id:125
LOCATION:  exec_stmt_raise,id:126
LOCATION:  exec_stmt_raise,pl_exec.c:2840
INSERT 0 1
-- 注意after for each row的触发器函数中,原始NEW.id值都是125,因此打印的修改后的NEW.id都=126.
-- after for each row触发器函数的返回值并不会影响returning的值,returning的值也是来自真正的行数据. (view的触发器场景除外)
postgres=# insert into t_ret values (123,now()) returning id;
NOTICE:  00000: tg01,pl_exec.c:2840
-- 如下,returning 的值为125,而不是126.
 id  
-----
 125
(1 row)
INSERT 0 1

-- DELETE before for each row 触发器函数 接收 OLD,修改这个record对结果的影响是什么?
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare
begin
  OLD.id := OLD.id+1; 
  raise notice '%,OLD.id; 
  return OLD;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger tg3 before DELETE ON t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# delete from t_ret where id=1;
DELETE 0
postgres=# delete from t_ret where id=2;
NOTICE:  00000: tg3,id:3
LOCATION:  exec_stmt_raise,pl_exec.c:2840
-- 虽然触发器函数返回的OLD.id=3,但是实际上删除的行是id=2的行. 说明delete before for each row的触发器函数返回值并不能改变行的定位. 与此不同的是,当delete before for each row触发器函数的返回值为空时,不会执行delete数据行的操作.
DELETE 1
postgres=# select * from t_ret ;
 id |  info  |          crt_time          
----+--------+----------------------------
  3 | digoal | 2013-03-10 17:33:32.91286
  3 | digoal | 2013-03-10 17:34:17.854048
(2 rows)
-- 返回空,不会执行delete操作,如下
postgres=# CREATE OR REPLACE FUNCTION public.tg_t_ret()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$                         
declare
begin
  OLD.id := OLD.id+1; 
  raise notice '%,OLD.id; 
  return null;
end;
$function$;
CREATE FUNCTION
postgres=# create trigger tg1 before delete on t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# delete from t_ret where id=1;
NOTICE:  00000: tg1,id:1
LOCATION:  exec_stmt_raise,pl_exec.c:2840
DELETE 0
-- 没有删除数据. 因为tg_t_ret触发器函数返回空.
postgres=# select * from t_ret;
 id | info |          crt_time          
----+------+----------------------------
  1 | abc  | 2013-03-10 20:38:50.951669
(1 row)
-- before for each row 的返回值不影响returning的值,returning 的值来自真实的受影响的行数据.
postgres=# delete from t_ret where id=100 returning *;
NOTICE:  00000: tg3,pl_exec.c:2840
NOTICE:  00000: tg3,pl_exec.c:2840
 id  |  info  |          crt_time          
-----+--------+----------------------------
 100 | digoal | 2013-03-10 17:50:04.680794
 100 | digoal | 2013-03-10 17:54:58.572425
(2 rows)
DELETE 2
-- 如上,DELETE before for each row触发器函数的返回值OLD.id=101,但是returning的值是被删除的行数据的值,因此ID=100.
-- 如下DELETE after for each row的返回值也不影响returning的返回值. returning的值取自真正被影响的行.
postgres=# create trigger tg04 after DELETE ON t_ret for each row execute procedure tg_t_ret();
CREATE TRIGGER
postgres=# delete from t_ret where id=123 returning *;
NOTICE:  00000: tg3,pl_exec.c:2840
NOTICE:  00000: tg04,id:124,触发器函数返回值OLD.id为124,returning id为123.
LOCATION:  exec_stmt_raise,pl_exec.c:2840
 id  |  info  |          crt_time          
-----+--------+----------------------------
 123 | digoal | 2013-03-10 17:55:02.809048
(1 row)
DELETE 1

-- UPDATE before for each row 触发器函数 接收 NEW 还是 OLD? 修改这个record对结果的影响是什么?
postgres=# drop table t_ret;
DROP TABLE
postgres=# create table t_ret(id int,crt_time timestamp);
CREATE TABLE
postgres=# insert into t_ret values (1,now()),(2,'DIGOAL',(3,(4,'abc',now());
INSERT 0 4
postgres=# create or replace function tg_t_ret() returns trigger as $$
declare
begin
  OLD.id := OLD.id+1;
  NEW.id := NEW.id+1;
  raise notice '%,old.id:%. new.id:%',OLD.id,NEW.id;
  return null;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger tg1 before update ON t_ret for each row execute PROCEDURE tg_t_ret();
CREATE TRIGGER
postgres=# create trigger tg2 before update ON t_ret for each row execute PROCEDURE tg_t_ret();
CREATE TRIGGER
-- update before for each row 触发器函数返回空,将不会执行update操作.
postgres=# update t_ret set info='new' where id=1 returning *;
NOTICE:  00000: tg1,old.id:2. new.id:2
LOCATION:  exec_stmt_raise,pl_exec.c:2840
 id | info | crt_time 
----+------+----------
(0 rows)
UPDATE 0

-- 当update before for each row 触发器函数的返回值不为空时,被更改的行的最终值将与最后一个执行的before for each row 触发器函数的返回值一致.
-- 本例的tg2是最后一次调用的update before for each row触发器函数,它的返回值为NEW.id := 3,NEW.info := 'new',NEW.crt_time保持原值.
-- 因此更新后的值id = 3,而不是原来的1.
postgres=# create or replace function tg_t_ret() returns trigger as $$ 
declare
begin
  OLD.id := OLD.id+1;
  NEW.id := NEW.id+1;
  raise notice '%,NEW.id;
  return NEW; 
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# update t_ret set info='new' where id=1 returning *;
NOTICE:  00000: tg1,pl_exec.c:2840
-- 前后两次调用的触发器函数OLD的值一样,说明OLD的值是从原始的HeapTuple拷贝过来的,而不是拷贝的指针.
-- 第一个update before for each row触发器函数中的NEW值一部分是从sql语句传入的(修改的值),另一部分是从原始的HeapTuple拷贝过来的(未修改的值).
-- 接下来的update before for each row 触发器函数则是上一个触发器函数的返回值.
-- 最终被更新的行数据则是最后一个update before for each row 触发器函数的返回值.
NOTICE:  00000: tg2,old.id:2. new.id:3
LOCATION:  exec_stmt_raise,pl_exec.c:2840
 id | info |          crt_time          
----+------+----------------------------
  3 | new  | 2013-03-10 20:56:52.339739
(1 row)
UPDATE 1
postgres=# select * from t_ret ;
 id |  info  |          crt_time          
----+--------+----------------------------
  2 | DIGOAL | 2013-03-10 20:56:52.339739
  3 | digoal | 2013-03-10 20:56:52.339739
  4 | abc    | 2013-03-10 20:56:52.339739
  3 | new    | 2013-03-10 20:56:52.339739
(4 rows)

-- 返回的record必须与触发这个触发器的表的结构一致. 否则无法转换成该tuple结构. 报错.
postgres=# create or replace function tg_t_ret() returns trigger as $$
declare 
  r record;
begin
  OLD.id := OLD.id+1;
  NEW.id := NEW.id+1;
  select 1 as id into r; 
  raise notice '%,NEW.id;
  return r;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# update t_ret set info='new' where id=2 returning *;
NOTICE:  00000: tg1,old.id:3. new.id:3
LOCATION:  exec_stmt_raise,pl_exec.c:2840
ERROR:  42804: returned row structure does not match the structure of the triggering table
DETAIL:  Number of returned columns (1) does not match expected column count (3).
CONTEXT:  PL/pgsql function tg_t_ret() during function exit
LOCATION:  convert_tuples_by_position,tupconvert.c:137
【小结1】
INSERT 触发器: for TABLE
1. before for each statement (触发器函数的返回值无意义)
2. before for each row (第一个被触发的触发器,触发器函数的NEW值取自sql语句)
    返回值的流水 : 
    2.1 返回值传递给下一个被触发的before for each row触发器,作为下一个触发器调用的触发器函数的NEW值.
    2.2 如果下面没有before for each row触发器,则将返回值传递给操作插入行数据的C函数. 
    2.3 如果返回值为空,那么跳过本行操作,(如果sql语句涉及多行,则跳到下一行的第一个before for each row触发器; 如果sql不涉及多行或者已经到达最后行,则直接跳到语句结束或after for each statement的操作;)
3. before for each row(可选)
4. 检查约束,插入行的操作
5. 以下触发器或returning语句的NEW值取自HeapTuple,表示物理的数据行中的数据,因此这里的触发器返回值没有意义,不会作为NEW值传递给其他触发器.
    after for each row 触发器 (>=0个)
    returning 语句,被插入的行的真实数据,其实就是最后一个before for each row触发器函数的返回值.
    after for each statement 触发器 (>=0个)
DELETE 触发器: for TABLE
1. before for each statement (触发器函数的返回值无意义)
2. before for each row (第一个被触发的触发器,触发器函数的OLD值取自sql语句)
    返回值的流水 : 
    2.1 返回值传递给下一个被触发的before for each row触发器,作为下一个触发器调用的触发器函数的OLD值.
    2.2 如果下面没有before for each row触发器,则进入DELETE行的操作,注意删除行不是通过上面返回的OLD值定位的,所以before for each row函数的返回值不会篡改删除行的操作. (注意它和INSERT触发器的分别,已经存在的数据(DELETE)和不存在的数据(INSERT)).
    2.3 如果返回值为空,删除行的操作
5. 以下触发器或returning语句的OLD值取自HeapTuple,不会作为OLD值传递给其他触发器.
    after for each row 触发器 (>=0个)
    returning 语句,被删除的行的原始数据,注意不是最后一个before for each row触发器函数的返回值.
    after for each statement 触发器 (>=0个)
UPDATE 触发器: for TABLE
1. before for each statement (触发器函数的返回值无意义)
2. before for each row (第一个被触发的触发器,触发器函数的OLD值和NEW值取自sql语句)
    返回值的流水 : 
    2.1 返回值传递给下一个被触发的before for each row触发器,作为下一个触发器调用的触发器函数的NEW值. OLD值修改不影响下一个触发器函数.
    2.2 如果下面没有before for each row触发器,则进入UPDATE行的操作,注意被更新的行不是通过触发器函数修改过的OLD值定位的,所以before for each row函数中对OLD值的修改不会篡改删除行的操作. (注意它和INSERT触发器的分别,已经存在的数据(DELETE)和不存在的数据(INSERT)).
    例如update t set info='new' where id=1; 如果在触发器中修改了OLD.id=2,不会变成update t set info='new' where id=2; 修改的行依然是1;
    2.3 如果返回值为空,则直接跳到语句结束或after for each statement的操作;)
3. before for each row(可选,上一个before for each row触发器函数的返回值影响这个触发器函数的NEW值,不影响OLD值)
4. 检查约束,删除行的操作,NEW值来自最后一个before for each row触发器函数的返回值.
5. 以下触发器或returning语句的NEW值取自HeapTuple,展示被更新的行的最终数据,其实就是最后一个before for each row触发器函数的返回值.
    after for each statement 触发器 (>=0个)

-- 视图触发器返回空测试
-- 创建基表
digoal=> create table tbl (id int,crt_time timestamp);
CREATE TABLE
-- 创建视图
digoal=> create view v_tbl as select * from tbl;
CREATE VIEW
-- 创建触发器函数
digoal=> create or replace function tg() returns trigger as $$
declare
begin
  case TG_OP
  when 'INSERT' then
    raise notice '%,%,new:%',TG_OP,TG_WHEN,TG_LEVEL,NEW;
  when 'UPDATE' then 
    raise notice '%,new:%,old:%',NEW,OLD;
  when 'DELETE' then
    raise notice '%,OLD;
  end case;
  return null;
end;
$$ language plpgsql;
CREATE FUNCTION
-- 创建触发器
digoal=> create trigger tg0 instead of insert or update or delete on v_tbl for each row execute procedure tg();
CREATE TRIGGER
digoal=> create trigger tg1 instead of insert or update or delete on v_tbl for each row execute procedure tg();
CREATE TRIGGER
-- 插入数据
digoal=> insert into v_tbl values (1,'digoal',now());
NOTICE:  INSERT,tg0,INSTEAD OF,ROW,new:(1,digoal,"2013-03-11 08:33:54.457727")
INSERT 0 0
-- ROW_COUNT为0.
-- before for each row触发器返回空,将导致后面的for each row 触发器不被触发(注意for each statement不会跳过),同时跳过对该行的操作. 
-- 数据未插入
digoal=> select * from tbl;
 id | info | crt_time 
----+------+----------
(0 rows)

-- 视图触发器返回record测试,NEW 或者OLD record修改后会带来什么影响?
-- 创建触发器函数
digoal=> create or replace function tg() returns trigger as $$
declare
begin
  case TG_OP
  when 'INSERT' then
    NEW.id := NEW.id+1;
    raise notice '%,NEW;
    return NEW;
  when 'UPDATE' then 
    NEW.id := NEW.id+1;
    OLD.id := OLD.id+1;
    raise notice '%,OLD;
    return NEW;
  when 'DELETE' then
    OLD.id := OLD.id+1;
    raise notice '%,OLD;
    return OLD;
  end case;
end;
$$ language plpgsql;
-- 插入测试,视图的最后一个被触发的instead of for each row触发器的返回值将对ROW_COUNT和RETURNING带来影响. 如下 : 
digoal=> insert into v_tbl values (1,now()) returning *;
NOTICE:  INSERT,new:(2,"2013-03-11 08:49:22.983877")
NOTICE:  INSERT,tg1,new:(3,"2013-03-11 08:49:22.983877")
 id |  info  |          crt_time          
----+--------+----------------------------
  3 | digoal | 2013-03-11 08:49:22.983877
(1 row)
INSERT 0 1
-- 最后一个触发器的返回值变成了returning的输出.
-- 最后一个触发器如果不返回空,则ROW_COUNT增1,如果返回空,ROW_COUNT则不增加.
digoal=> select * from tbl;
 id | info | crt_time 
----+------+----------
(0 rows)

-- 需要注意OLD的修改不会传递给下一个instead for each row触发器函数,也不会传递给操作行的C函数,也不会传递给returning. 
-- 基表数据插入
digoal=> insert into tbl values (1,now());
INSERT 0 1
-- 基表数据
digoal=> select * from tbl;
 id |  info  |          crt_time          
----+--------+----------------------------
  1 | digoal | 2013-03-11 08:56:20.326402
(1 row)
-- 删除操作,触发器函数的返回值为OLD. 但是显然没有传递给下一个触发器函数的OLD变量.
-- 因为两次修改后的OLD.id都是2,如果传递过去的话,第二次修改后的OLD.id应该是3
digoal=> delete from v_tbl where id=1 returning *;
NOTICE:  DELETE,old:(2,"2013-03-11 08:56:20.326402")
NOTICE:  DELETE,"2013-03-11 08:56:20.326402")
 id |  info  |          crt_time          
----+--------+----------------------------
  1 | digoal | 2013-03-11 08:56:20.326402
(1 row)
DELETE 1
-- 因为返回值不为空,所以row_count变量增1,同时returning的值来自真实的行数据. 而不是OLD的值.
digoal=> select * from tbl;
 id |  info  |          crt_time          
----+--------+----------------------------
  1 | digoal | 2013-03-11 08:56:20.326402
(1 row)
-- 因为id=2不存在,所以不会触发instead of for each row触发器.
digoal=> delete from v_tbl where id=2 returning *;
 id | info | crt_time 
----+------+----------
(0 rows)
DELETE 0

-- 以上触发器函数修改一下,可以实现修改视图,并且想修改表一样可以正常返回ROW_COUNT和RETURNING.
create or replace function tg() returns trigger as $$
declare
begin
  case TG_OP
  when 'INSERT' then
    insert into tbl values (NEW.*);
    raise notice '%,NEW;
    return NEW;
  when 'UPDATE' then 
    delete from tbl where tbl.* = OLD.*;
    insert into tbl values (NEW.*);
    raise notice '%,OLD;
    return NEW;
  when 'DELETE' then
    delete from tbl where tbl.* = OLD.*;
    raise notice '%,OLD;
    return OLD;
  end case;
end;
$$ language plpgsql;

【小结2】
1. 给视图触发器函数添加返回值,可以令视图的DML操作和操作表一样正常返回ROW_COUNT和RETURNING值.
2. 当一个视图上创建了多个instead of for each row触发器时,触发器函数的返回值将传递给下一个被调用的instead of for each row触发器函数的NEW变量,(OLD不传递).

【小结3】
1. 哪些触发器函数的返回值没有意义?
    -- for each statement的触发器函数的返回值没有意义,不会造成任何影响. 不管是返回NULL还是HeapTuple都无意义,所以返回NULL就可以了.
    -- after for each row 的触发器函数的返回值也没有意义,所以返回NULL就可以了.
    -- 因此有意义的就是before for each row的触发器函数的返回值. 
    -- before for each row触发器函数返回NULL将造成跳过该行的操作,同时跳过后面所有的for each row触发器.
    -- before for each row触发器函数返回HeapTuple时,返回值将传递给下一个before for each row的触发器函数的NEW,或者行操作的C函数.
    -- 注意OLD不会传递给下一个触发器函数或操作行的C函数.


6. 触发器函数的返回值与返回行数的关系,RETURNING的关系.
FOUND和ROW_COUNT在plpgsql函数中使用 : 
如果表的before for each row触发器函数返回空,将导致跳过该行处理,因此如果整个sql的行都跳过处理那么最终的FOUND=false,ROW_COUNT=0;
视图的instead of for each row触发器函数返回空,一样,如果整个sql的行都跳过处理那么最终的FOUND=false,ROW_COUNT=0;
表的returning 取真正被操作的行的最终数据.
视图的returning 取最后一个instead of for each row触发器函数的返回值.
FOUND和ROW_COUNT的用法如下 : 
There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command,which has the form:

GET [ CURRENT ] DIAGNOSTICS variable = item [,... ];
This command allows retrieval of system status indicators. Each item is a key word identifying a status value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT,the number of rows processed by the last sql command sent to the sql engine,and RESULT_OID,the OID of the last row inserted by the most recent sql command. Note that RESULT_OID is only useful after an INSERT command into a table containing OIDs.

An example:

GET DIAGNOSTICS integer_var = ROW_COUNT;
The second method to determine the effects of a command is to check the special variable named FOUND,which is of type boolean. FOUND starts out false within each PL/pgsqlfunction call. It is set by each of the following types of statements:

A SELECT INTO statement sets FOUND true if a row is assigned,false if no row is returned.

A PERFORM statement sets FOUND true if it produces (and discards) one or more rows,false if no row is produced.

UPDATE,INSERT,and DELETE statements set FOUND true if at least one row is affected,false if no row is affected.

A FETCH statement sets FOUND true if it returns a row,false if no row is returned.

A MOVE statement sets FOUND true if it successfully repositions the cursor,false otherwise.

A FOR or FOREACH statement sets FOUND true if it iterates one or more times,else false. FOUND is set this way when the loop exits; inside the execution of the loop,FOUND is not modified by the loop statement,although it might be changed by the execution of other statements within the loop body.

RETURN QUERY and RETURN QUERY EXECUTE statements set FOUND true if the query returns at least one row,false if no row is returned.

Other PL/pgsql statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS,but does not change FOUND.

FOUND is a local variable within each PL/pgsql function; any changes to it affect only the current function.

参见 : 
http://www.postgresql.org/docs/9.2/static/plpgsql-statements.html

7. 触发器的延时属性和状态.
当触发器为约束触发器时,可以增加延时属性,约束触发器必须创建为after for each row触发器.
延时触发指放在事务结束时触发.
非延时触发指放在sql语句结束时触发.
When the CONSTRAINT option is specified,this command creates a constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers. They can be fired either at the end of the statement causing the triggering event,or at the end of the containing transaction; in the latter case they are said to be deferred. A pending deferred-trigger firing can also be forced to happen immediately by using SET CONSTRAINTS. Constraint triggers are expected to raise an exception when the constraints they implement are violated.



Note that for constraint triggers,evaluation of the WHEN condition is not deferred,but occurs immediately after the row update operation is performed. If the condition does not evaluate to true then the trigger is not queued for deferred execution.

触发器还有一个和会话参数session_replication_role结合使用的隐含特性,需要使用ALTER TABLE来修改,如下 : 
session_replication_role (enum)
Controls firing of replication-related triggers and rules for the current session. Setting this variable requires superuser privilege and results in discarding any prevIoUsly cached query plans. 
Possible values are origin (the default),replica and local. 
See ALTER TABLE for more information.

DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system,but is not executed when its triggering event occurs. For a deferred trigger,the enable status is checked when the event occurs,not when the trigger function is actually executed. 
One can disable or enable a single trigger specified by name,or all triggers on the table,or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). 
Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed. 
The trigger firing mechanism is also affected by the configuration variable session_replication_role. 
Simply enabled triggers will fire when the replication role is "origin" (the default) or "local". 
Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode,and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.
如果在表上面执行enable replica trigger $tg_name,那么这个触发器只有当会话参数session_replication_role=replica时,才会被触发.
如果会话参数session_replication_role=origin或者local,这个触发器将不会被触发.
如果在表上面执行enable always trigger $tg_name,那么这个触发器不管会话参数session_replication_role的值是什么,都会被触发.
其实这个用法还可以通过application_name参数以及触发器中配置判断application_name的控制语句来实现,当然效率没有上面的方法高 : 
例如 : 
pgsql中的application_name可以这么来修改 : 
-- 连接参数中修改
ocz@db-172-16-3-150-> psql postgresql://:9201/digoal?application_name=digoal
psql (9.2.1)
Type "help" for help.
digoal=# show application_name;
 application_name 
------------------
 digoal
(1 row)
digoal=# \q
-- 会话中修改 : 
ocz@db-172-16-3-150-> psql
psql (9.2.1)
Type "help" for help.
postgres=# show application_name;
 application_name 
------------------
 psql
(1 row)
postgres=# set application_name='abc';
SET
postgres=# show application_name;
 application_name 
------------------
 abc
(1 row)
使用application_name实现触发器内部控制 : 
digoal=> create table abc(id int,info text);
CREATE TABLE
digoal=> create or replace function tg() returns trigger as $$
declare
  v_app_name text;
begin
  select setting into v_app_name from pg_settings where name='application_name';
  if v_app_name='digoal' then
    return null;
  end if;
  return NEW;
end;
$$ language plpgsql;
CREATE FUNCTION
digoal=> create trigger tg0 before insert on abc for each row execute procedure tg();
CREATE TRIGGER
digoal=> select setting from pg_settings where name='application_name';
 setting 
---------
 psql
(1 row)
digoal=> insert into abc values(1,'digoal');
INSERT 0 1
-- 当application_name='digoal'时不插入数据.
digoal=> set application_name='digoal';
SET
digoal=> insert into abc values(1,'digoal');
INSERT 0 0
使用session_replication_role来控制触发器是否被触发.
digoal=> create table abc(id int,info text);
CREATE TABLE
digoal=> create or replace function tg() returns trigger as $$
declare
begin
  raise notice '%',TG_NAME;
  return NEW;
end;
$$ language plpgsql;
CREATE FUNCTION
digoal=> alter table abc enable replica trigger tg0;
ALTER TABLE
-- 当session_replication_role=origin并且trigger tg0 修改为enable replica时,触发器未被触发.
digoal=> show session_replication_role;
 session_replication_role 
--------------------------
 origin
(1 row)
digoal=> insert into abc values (1,'digoal');
INSERT 0 1

-- set session_replication_role需要超级用户权限 : 
digoal=> \c digoal postgres
You are now connected to database "digoal" as user "postgres".
-- 当session_replication_role=replica并且trigger tg0 修改为enable replica时,触发器被触发了.
digoal=# set session_replication_role='replica';
SET
digoal=# insert into digoal.abc values (1,'digoal');
NOTICE:  tg0
INSERT 0 1

8. 可以在系统表或系统视图上创建触发器吗?
不可以,如下 : 
digoal=# create trigger tg before insert on pg_database for each row execute procedure tg();
ERROR:  42501: permission denied: "pg_database" is a system catalog
LOCATION:  CreateTrigger,trigger.c:195
详见
src/backend/commands/trigger.c

【小结】
1. 注意各种触发器在操作流中的顺序,返回值的传递,返回值的意义.
2. 注意当1个表上有多个同类触发器时,需要注意他们之间的参数传递,触发顺序.
3. 还要注意触发器的可视,下一篇中会着重讲可视特性.

【参考】
1. http://www.postgresql.org/docs/9.2/static/trigger-definition.html
2. http://www.postgresql.org/docs/9.2/static/trigger-datachanges.html
3. http://www.postgresql.org/docs/9.2/static/spi-visibility.html
4. http://www.postgresql.org/docs/9.2/static/trigger-example.html
5. http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
6. http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html
7. http://www.postgresql.org/docs/9.2/static/trigger-interface.html
8. http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html
9. src/backend/commands/trigger.c
10. src/include/commands/trigger.h
11. src/include/utils/reltrigger.h
12. 触发器的应用 : 
http://blog.163.com/digoal@126/blog/static/16387704020128772037884/
http://blog.163.com/digoal@126/blog/static/16387704020120133019990/
http://blog.163.com/digoal@126/blog/static/163877040201251931517556/
http://blog.163.com/digoal@126/blog/static/16387704020130931040444/
http://blog.163.com/digoal@126/blog/static/163877040201301483549300/
http://blog.163.com/digoal@126/blog/static/1638770402012325111528424/
http://blog.163.com/digoal@126/blog/static/163877040201211193542316/
http://blog.163.com/digoal@126/blog/static/1638770402012731203716/
http://blog.163.com/digoal@126/blog/static/1638770402012731944439/
http://blog.163.com/digoal@126/blog/static/16387704020128142829610/
http://blog.163.com/digoal@126/blog/static/16387704020129851138327/
http://blog.163.com/digoal@126/blog/static/163877040201119111234570/


[https://yq.aliyun.com/articles/2261](https://yq.aliyun.com/articles/2261)
原文链接：https://www.f2er.com/postgresql/193598.html
postgresql 触发器 简介（转）

猜你在找的Postgre SQL相关文章

postgresql 触发器简介（转）
