PostgreSQL 触发器 用法详解 2

背景

触发器在数据库中的应用非常广泛，例如在发生insert, update, delete, truncate时触发，调用事先写好的触发器函数。

触发器函数可以使用plpgsql , c , python , perl , java等语言编写。

触发器函数中支持很多内置的变量，可以干很多事情。

本文将以PostgreSQL 9.2为例, 介绍触发器的使用. 分两部分.

第一部分链接 :

《PostgreSQL 触发器 用法详解 1》

内容简介

一、

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的结果的?

正文

二、

以plpgsql语言为例, 讲解触发器函数.

9. 触发器函数的返回值.

上一篇中有大量的例子进行讲解, 文章末尾也做了总结, 这里就略过了.

稍微小结一下,

[返回值的类型 : ]

空 或者 触发器函数所在表或视图的结构化数据(record类型).

[返回值的意义 : ]

    -- for each statement的触发器函数的返回值没有意义, 不会造成任何影响. 不管是返回NULL还是HeapTuple都无意义, 所以返回NULL就可以了.  
    -- after for each row 的触发器函数的返回值也没有意义, 不会造成任何影响. 不管是返回NULL还是HeapTuple都无意义, 所以返回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函数.  

10. 触发器函数的参数与值传递.

这里说的是plpgsql触发器函数的参数, 不是放在形参定义中, 而是使用TG_ARGV[]来获取.

参数的值传递 :

在创建触发器时传递, 以STRING常量类型传递, 如果不是string类型, 会自动转换成string类型.

arguments  
  
An optional comma-separated list of arguments to be provided to the function when the trigger is executed.   
The arguments are literal string constants. Simple names and numeric constants can be written here, too, but they will all be converted to strings.   
Please check the description of the implementation language of the trigger function to find out how these arguments can be accessed within the function;   
it might be different from normal function arguments.  

例如 :

digoal=> create or replace function tg() returns trigger as $$  
declare  
begin  
  raise notice 'args:%', TG_NARGS;   
  if TG_NARGS>0 then  
    for i in 0..(TG_NARGS-1) loop  
      raise notice 'ARG%: %', i, TG_ARGV[i];  
    end loop;  
  end if;  
  return new;  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
digoal=> create table arg_test (id int, info text, crt_time timestamp);  
CREATE TABLE  

– 参数是字符串常量, 不是变量.

digoal=> create trigger tg0 before insert on arg_test for each row execute procedure tg(id, info, crt_time);  
CREATE TRIGGER  
digoal=> insert into arg_test values (1,'digoal',now());  
NOTICE:  args:3  
NOTICE:  ARG0: id  
NOTICE:  ARG1: info  
NOTICE:  ARG2: crt_time  
INSERT 0 1  

11. 触发器函数中的变量与传递.

特殊变量, 这些变量自动被创建 :

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:  
  
NEW  
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETEoperations.  
  
OLD  
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERToperations.  
  
TG_NAME  
Data type name; variable that contains the name of the trigger actually fired.  
  
TG_WHEN  
Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.  
  
TG_LEVEL  
Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.  
  
TG_OP  
Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.  
  
TG_RELID  
Data type oid; the object ID of the table that caused the trigger invocation.  
  
TG_RELNAME  
Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.  
  
TG_TABLE_NAME  
Data type name; the name of the table that caused the trigger invocation.  
  
TG_TABLE_SCHEMA  
Data type name; the name of the schema of the table that caused the trigger invocation.  
  
TG_NARGS  
Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.  
  
TG_ARGV[]  
Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.  

其他变量 :

与plpgsql函数的用法一致.

declare 中声明.

begin 中使用.

这里不详细讲解.

12. 触发器函数的数据可视与什么有关?

在触发器函数中执行SQL时, 需要特别注意数据的可视, 是否能看到触发这个触发器的SQL语句前面已经修改过的行数据.

If you execute SQL commands in your trigger function, and these commands access the table that the trigger is for,   
then you need to be aware of the data visibility rules,   
because they determine whether these SQL commands will see the data change that the trigger is fired for. Briefly:  
  
Statement-level triggers follow simple visibility rules:   
none of the changes made by a statement are visible to statement-level triggers that are invoked before the statement,   
whereas all modifications are visible to statement-level AFTER triggers.  

before for each statement 不能看到任何数据更改.

after for each statement 能看到所有的数据更改.

The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level BEFORE trigger, because it hasn't happened yet.  
before for each row触发器不能看到insert , update , delete对本行数据的更改. 因为本行数据变更尚未发生.  
  
However, SQL commands executed in a row-level BEFORE trigger will see the effects of data changes for rows previously processed in the same outer command. This requires caution, since the ordering of these change events is not in general predictable; a SQL command that affects multiple rows can visit the rows in any order.  
before for each row触发器可以看到其他已经变更的行的数据, 因为一条SQL语句可能操作多条数据, 后面的行触发器可以看到前面已经变更的行数据.   
  
Similarly, a row-level INSTEAD OF trigger will see the effects of data changes made by previous firings of INSTEAD OF triggers in the same outer command.  
instead of 触发器与上面类似, 可以看到其他已经变更的行的数据, 因为一条SQL语句可能操作多条数据, 后面的行触发器可以看到前面已经变更的行数据.   
  
When a row-level AFTER trigger is fired, all data changes made by the outer command are already complete, and are visible to the invoked trigger function.  
注意, after for each row触发器函数可以看到所有的数据变更, 因为after for each row触发器是在所有行都操作完后触发的.   

例如SQL语句更新10条记录, 那么是这样的流程 :

1. before for each statement(触发1次)  
  
2. 循环10次  
  
{  
  before for each row (触发1次)  
  检查约束, 操作行.  
}  
  
3. after for each row(触发10次)  
4. after for each statement(触发1次)  

If your trigger function is written in any of the standard procedural languages, then the above statements apply only if the function is declared VOLATILE.   
Functions that are declared STABLE or IMMUTABLE will not see changes made by the calling command in any case.  

以上对变更数据的可视仅限于函数为volatile, 当触发器函数为stable或者immutable时, 任何情况下都不能看到已经变更的数据.

举例 :

– 创建测试表

digoal=> create table test(id int);  
CREATE TABLE  

– 创建触发器函数

digoal=> create or replace function tg() returns trigger as $$                                
declare  
  cnt int8;  
begin  
  select count(*) into cnt from test where id=1;  
  raise notice '%, %, %, %, cnt:%', TG_LEVEL, TG_WHEN, TG_OP, TG_NAME, cnt;  
  return new;  
end;  
$$ language plpgsql volatile;  
CREATE FUNCTION  

– 创建触发器

digoal=> create trigger tg0 before insert on test for each statement execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg1 after insert on test for each statement execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg2 before insert on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg3 before insert on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg4 before insert on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg5 after insert on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg6 after insert on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg7 after insert on test for each row execute procedure tg();  
CREATE TRIGGER  

– 插入1行数据

digoal=> insert into test values (1);  
NOTICE:  STATEMENT, BEFORE, INSERT, tg0, cnt:0  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:0  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:0  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:0  
-- after for each row看到了已经插入的数据.  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:1  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:1  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:1  
NOTICE:  STATEMENT, AFTER, INSERT, tg1, cnt:1  
INSERT 0 1  

– 插入多行数据

digoal=> insert into test values (1),(1),(1);  
NOTICE:  STATEMENT, BEFORE, INSERT, tg0, cnt:1  
-- 第一行触发以下触发器.  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:1  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:1  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:1  
-- 第2行触发以下触发器.  
-- 能够看到前面1条变更.  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:2  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:2  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:2  
-- 第3行触发以下触发器.  
-- 能够看到前面2条变更.  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:3  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:3  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:3  
-- 以下分别为3行after for each row触发的触发器.  
-- after for each row触发器是在所有的before for each row都调用完后, 也就是SQL语句的所有的行操作完成后调用的, 每行调用1次.  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:4  
  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:4  
  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:4  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:4  
-- 最后为after for each statement触发器, 可以看到所有变更.  
NOTICE:  STATEMENT, AFTER, INSERT, tg1, cnt:4  
INSERT 0 3  

UPDATE :

digoal=> create trigger tg8 before update on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg9 before update on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg10 after update on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> update test set id=2;  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:4  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:4  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:3  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:3  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:2  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:2  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:1  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:1  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:0  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:0  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:0  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:0  
UPDATE 4  
  
digoal=> update test set id=1;  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:0  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:0  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:1  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:1  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:2  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:2  
NOTICE:  ROW, BEFORE, UPDATE, tg8, cnt:3  
NOTICE:  ROW, BEFORE, UPDATE, tg9, cnt:3  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:4  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:4  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:4  
NOTICE:  ROW, AFTER, UPDATE, tg10, cnt:4  
UPDATE 4  

DELETE :

digoal=> create trigger tg12 before delete on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg13 before delete on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg14 after delete on test for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create or replace function tg() returns trigger as $$                                
declare  
  cnt int8;  
begin  
  select count(*) into cnt from test where id=1;  
  raise notice '%, %, %, %, cnt:%', TG_LEVEL, TG_WHEN, TG_OP, TG_NAME, cnt;  
  return old;  
end;  
$$ language plpgsql volatile;  
digoal=> delete from test where id=1;  
NOTICE:  ROW, BEFORE, DELETE, tg12, cnt:3  
NOTICE:  ROW, BEFORE, DELETE, tg13, cnt:3  
NOTICE:  ROW, BEFORE, DELETE, tg12, cnt:2  
NOTICE:  ROW, BEFORE, DELETE, tg13, cnt:2  
NOTICE:  ROW, BEFORE, DELETE, tg12, cnt:1  
NOTICE:  ROW, BEFORE, DELETE, tg13, cnt:1  
NOTICE:  ROW, AFTER, DELETE, tg14, cnt:0  
NOTICE:  ROW, AFTER, DELETE, tg14, cnt:0  
NOTICE:  ROW, AFTER, DELETE, tg14, cnt:0  
DELETE 3  

触发器函数改为stable :

重新以上测试 :

digoal=> alter function tg() stable;  
ALTER FUNCTION  
digoal=> select * from test ;  
 id   
----  
  1  
  1  
  1  
  1  
  1  
(5 rows)  
digoal=> insert into test values (1),(1),(1),(1);  
NOTICE:  STATEMENT, BEFORE, INSERT, tg0, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg2, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg3, cnt:5  
NOTICE:  ROW, BEFORE, INSERT, tg4, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg5, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg6, cnt:5  
NOTICE:  ROW, AFTER, INSERT, tg7, cnt:5  
NOTICE:  STATEMENT, AFTER, INSERT, tg1, cnt:5  
INSERT 0 4  

– 触发器函数改为stable或immutable后, 看不到SQL语句中发生的变更.

13. 触发器会无限递归吗? 如何避免?

会, 例如 :

digoal=> create table rec (id int);  
CREATE TABLE  
digoal=> create or replace function tg() returns trigger as $$                                
declare  
begin        
  -- 这里将会造成无限递归.  
  insert into rec values(2);  
  raise notice '%, %, %, %, cnt:%', TG_LEVEL, TG_WHEN, TG_OP, TG_NAME, cnt;  
  return new;                                                                
end;           
$$ language plpgsql volatile;  
CREATE FUNCTION  
digoal=> create trigger tg0 before insert on rec for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> insert into rec values (1);  
ERROR:  54001: stack depth limit exceeded  
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.  
CONTEXT:  SQL statement "insert into rec values(2)"  
PL/pgSQL function tg() line 4 at SQL statement  
SQL statement "insert into rec values(2)"  
LOCATION:  check_stack_depth, postgres.c:3076  

– 不要在触发器函数中执行会再次触发触发器的SQL, 防止无限递归.

14. 触发条件与性能.

这里指的是创建触发器时在WHEN中指定的触发条件. 在上一篇BLOG中已经提到了. 如下 :

表触发器 :

(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)  

视图触发器 :

(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和在触发器中使用条件控制两者的性能有什么分别呢?

A trigger definition can also specify a Boolean WHEN condition, which will be tested to see whether the trigger should be fired.   
  
In row-level triggers the WHEN condition can examine the old and/or new values of columns of the row.   
  
(Statement-level triggers can also have WHEN conditions, although the feature is not so useful for them.)   
  
In a BEFORE trigger, the WHEN condition is evaluated just before the function is or would be executed,   
so using WHEN is not materially different from testing the same condition at the beginning of the trigger function.   
  
However, in an AFTER trigger, the WHEN condition is evaluated just after the row update occurs, and it determines whether an event is queued to fire the trigger at the end of statement.   
  
So when an AFTER trigger's WHEN condition does not return true, it is not necessary to queue an event nor to re-fetch the row at end of statement.   
  
This can result in significant speedups in statements that modify many rows, if the trigger only needs to be fired for a few of the rows.   
  
INSTEAD OF triggers do not support WHEN conditions.  

能够带来性能提升的是当SQL语句带来多行的变更时, after for each row触发器中加判断条件或者使用WHEN这两种方法的性能存在极大差异.

因为after for each row触发器创建时的WHEN条件是在该行操作发生后进行的, 如果不为TRUE则不会把这个事件放入QUEUE中供后面的after for each row触发器使用. 并且在after for each statement触发器前, 也不需要获取已变更的行数据.

如果不放在WHEN中, 那么每一被变更的行都需要在after for each statement触发器前, 获取已变更的行数据, 供after for each row触发器函数使用.

例如 :

digoal=> create table perf (id int);  
CREATE TABLE  
digoal=> insert into perf select generate_series(1,10000);  
INSERT 0 10000  
digoal=> create or replace function tg() returns trigger as $$  
declare  
begin  
  raise notice '%, %, %, %, old:%', TG_LEVEL, TG_WHEN, TG_OP, TG_NAME, OLD;  
  return old;  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
digoal=> create trigger tg0 after delete on perf for each row when (old.id=10) execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg1 before delete on perf for each row when (old.id=10) execute procedure tg();  
CREATE TRIGGER  

– 测试

digoal=> delete from perf where id<15 returning *;  
NOTICE:  00000: ROW, BEFORE, DELETE, tg1, old:(10)  
LOCATION:  exec_stmt_raise, pl_exec.c:2840  
NOTICE:  00000: ROW, AFTER, DELETE, tg0, old:(10)  
LOCATION:  exec_stmt_raise, pl_exec.c:2840  
 id   
----  
  1  
  2  
  3  
  4  
  5  
  6  
  7  
  8  
  9  
 10  
 11  
 12  
 13  
 14  
(14 rows)  
DELETE 14  

– 从上面来看好像BEFORE和AFTER FOR EACH ROW使用WHEN时都有效率提高, 实际上after 的提高更明显.

请看如下流程 :

id=1  
before for each row when (false)  
after for each row when (false)  
id=2  
before for each row when (false)  
after for each row when (false)  
....  
id=10  
before for each row when (true)  
after for each row when (true)  
...  
id=14  
before for each row when (false)  
after for each row when (false)  

修改触发器函数, 判断放到函数中 :

digoal=> create or replace function tg() returns trigger as $$  
declare  
begin  
  if OLD.id=1 then   
    raise notice '%, %, %, %, old:%', TG_LEVEL, TG_WHEN, TG_OP, TG_NAME, OLD;  
    return old;   
  else   
    return null;   
  end if;  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
digoal=> drop trigger tg0 on perf;  
DROP TRIGGER  
digoal=> drop trigger tg1 on perf;  
DROP TRIGGER  
digoal=> create trigger tg1 before delete on perf for each row execute procedure tg();  
CREATE TRIGGER  
digoal=> create trigger tg0 after delete on perf for each row execute procedure tg();  
CREATE TRIGGER  

– 测试

digoal=> insert into perf select generate_series(1,10000);  
INSERT 0 10000  
digoal=> delete from perf where id<15 returning *;  
NOTICE:  00000: ROW, BEFORE, DELETE, tg1, old:(1)  
LOCATION:  exec_stmt_raise, pl_exec.c:2840  
NOTICE:  00000: ROW, AFTER, DELETE, tg0, old:(1)  
LOCATION:  exec_stmt_raise, pl_exec.c:2840  
 id   
----  
  1  
(1 row)  
DELETE 1  

流程如下 :

id=1  
before for each row  
行操作, 并放入event queue  
  
id=2  
before for each row  
无行操作  
  
....  
  
id=14  
before for each row  
无行操作  
  
  
id=1  
after for each row  
  
如果把tg1删掉的话, 情况就发生了变化 :   
  
id=1  
before for each row  
  
行操作, 并放入event queue  
  
id=2  
before for each row  
  
行操作, 并放入event queue  
....  
  
id=14  
before for each row  
行操作, 并放入event queue  
  
  
id=1  
after for each row  
id=2  
after for each row  
....  
id=14  
after for each row  

因此when 在多行处理中对 仅需要少量行触发after for each row触发器函数的场景 性能提升会非常明显.

15. 加入触发器后的事务特性.

加入触发器后, 如果触发器函数抛出异常, 那么整个事务回滚.

例如 :

digoal=> create table err (id int);  
CREATE TABLE  
digoal=> create or replace function tg() returns trigger as $$  
declare  
begin  
  raise exception '';  
  return new;  
end;  
$$ language plpgsql;  
CREATE FUNCTION  
digoal=> create trigger tg0 after insert on err for each statement execute procedure tg();  
CREATE TRIGGER  
digoal=> insert into err values (1);  
ERROR:  P0001:   
LOCATION:  exec_stmt_raise, pl_exec.c:2840  
digoal=> select * from err;  
 id   
----  
(0 rows)  

16. 触发器函数的返回值是如何影响returning的结果的?

上一篇blog已经详细讲解. 这里略.

小结

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/

digoal’s 大量PostgreSQL文章入口