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
typedef struct TriggerData
{
NodeTag type;
TriggerEvent tg_event;
Relation tg_relation;
HeapTuple tg_trigtuple;
HeapTuple tg_newtuple;
Trigger *tg_trigger;
Buffer tg_trigtuplebuf;
Buffer tg_newtuplebuf;
} TriggerData;
/*Trigger数据结构, 传入的参数数据结构 : */
typedef struct Trigger
{
Oid tgoid; /* OID of trigger (pg_trigger row) */
/* Remaining fields are copied from pg_trigger, see pg_trigger.h */
char *tgname;
Oid tgfoid;
int16 tgtype;
char tgenabled;
bool tgisinternal;
Oid tgconstrrelid;
Oid tgconstrindid;
Oid tgconstraint;
bool tgdeferrable;
bool tginitdeferred;
int16 tgnargs;
int16 tgnattr;
int16 *tgattr;
char **tgargs;
char *tgqual;
} Trigger;
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, UPDATE, 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, UPDATE, 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 — —
– 把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,’digoal’,now()); NOTICE: 00000: tg01 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg1 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 NOTICE: 00000: tg4 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,now()); NOTICE: 00000: tg01 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg1 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 NOTICE: 00000: tg4 LOCATION: exec_stmt_raise, 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, info text, crt_time timestamp); CREATE TABLE – 创建触发器函数, 修改NEW并返回. postgres=# create or replace function tg_t_ret() returns trigger as
declarebeginNEW.id:=NEW.id+1;raisenotice′−−修改NEW.id,并返回修改后的NEW.影响插入数据的并不是NEW变量本身,而是return的值,这个在后面将会有例子举证.returnNEW;end;
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,’digoal’,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值.rrecord;begin−−将NEW赋予给r.r:=NEW;NEW.id:=NEW.id+1;raisenotice′−−修改NEW.id,并返回修改前的NEW.returnr;end;
declare -- r用来记录原始的NEW值, 并返回原始的NEW值. r record; begin -- 将NEW赋予给r. r := NEW; NEW.id := NEW.id+1; raise notice '%, id:%', TG_NAME, NEW.id; -- 修改NEW.id , 并返回修改前的NEW . return r; end; language plpgsql; CREATE FUNCTION – 插入测试数据 postgres=# insert into t_ret values (100,’digoal’,now()); NOTICE: 00000: tg01, 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, id:101 LOCATION: exec_stmt_raise, 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, id:101 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg2, id:101 LOCATION: exec_stmt_raise, 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
declarerrecord;beginNEW.id:=NEW.id+1;raisenotice′−−修改NEW.id,并返回修改后的NEW.returnNEW;end;
declare r record; begin NEW.id := NEW.id+1; raise notice '%, id:%', TG_NAME, NEW.id; -- 修改NEW.id , 并返回修改后的NEW . return NEW; end; language plpgsql; CREATE FUNCTION – 插入测试数据 postgres=# insert into t_ret values (123,’digoal’,now()); NOTICE: 00000: tg01, id:124 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg1, id:125 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg02, id:126 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg2, 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,’digoal’,now()) returning id; NOTICE: 00000: tg01, id:124 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg1, id:125 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg02, id:126 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg2, id:126 LOCATION: exec_stmt_raise, pl_exec.c:2840 – 如下, returning 的值为125, 而不是126.
125 (1 row) INSERT 0 1
– DELETE before for each row 触发器函数 接收 OLD, 修改这个record对结果的影响是什么? postgres=# create or replace function tg_t_ret() returns trigger as
declarebeginOLD.id:=OLD.id+1;raisenotice′returnOLD;end;
declare begin OLD.id := OLD.id+1; raise notice '%, id:%', TG_NAME, 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 functionfunction declare begin OLD.id := OLD.id+1; raise notice ‘%, id:%’, TG_NAME, OLD.id; return null; end; functionfunction; 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, id:101 LOCATION: exec_stmt_raise, pl_exec.c:2840 NOTICE: 00000: tg3, id:101 LOCATION: exec_stmt_raise, 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, id:124 LOCATION: exec_stmt_raise, 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, info text, crt_time timestamp); CREATE TABLE postgres=# insert into t_ret values (1,’digoal’,now()), (2,’DIGOAL’,now()), (3,’digoal’,now()), (4,’abc’,now()); INSERT 0 4 postgres=# create or replace function tg_t_ret() returns trigger as
declarebeginOLD.id:=OLD.id+1;NEW.id:=NEW.id+1;raisenotice′returnnull;end;
declare begin OLD.id := OLD.id+1; NEW.id := NEW.id+1; raise notice '%, old.id:%. new.id:%', TG_NAME, 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
declarebeginOLD.id:=OLD.id+1;NEW.id:=NEW.id+1;raisenotice′returnNEW;end;
declare begin OLD.id := OLD.id+1; NEW.id := NEW.id+1; raise notice '%, old.id:%. new.id:%', TG_NAME, OLD.id, NEW.id; return NEW; end; language plpgsql; CREATE FUNCTION 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 – 前后两次调用的触发器函数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
declarerrecord;beginOLD.id:=OLD.id+1;NEW.id:=NEW.id+1;select1asidintor;raisenotice′returnr;end;
declare r record; begin OLD.id := OLD.id+1; NEW.id := NEW.id+1; select 1 as id into r; raise notice '%, old.id:%. new.id:%', TG_NAME, OLD.id, 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 如果返回值为空, 那么跳过本行操作, (如果SQL语句涉及多行, 则跳到下一行的第一个before for each row触发器; 如果SQL不涉及多行或者已经到达最后行, 则直接跳到语句结束或after for each statement的操作;) 3. before for each row(可选) 4. 检查约束, 删除行的操作 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 如果返回值为空, 那么跳过本行操作, (如果SQL语句涉及多行, 则跳到下一行的第一个before for each row触发器; 如果SQL不涉及多行或者已经到达最后行, 则直接跳到语句结束或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, 表示物理的数据行中的数据, 因此这里的触发器返回值没有意义, 不会作为NEW值传递给其他触发器. after for each row 触发器 (>=0个) returning 语句, 展示被更新的行的最终数据, 其实就是最后一个before for each row触发器函数的返回值. after for each statement 触发器 (>=0个)
– 视图触发器返回空测试 – 创建基表 digoal=> create table tbl (id int, info text, 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
declarebegincaseTGOPwhen′INSERT′thenraisenotice′when′UPDATE′thenraisenotice′when′DELETE′thenraisenotice′endcase;returnnull;end;
declare begin case TG_OP when 'INSERT' then raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW; when 'UPDATE' then raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD; when 'DELETE' then raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, 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
declarebegincaseTGOPwhen′INSERT′thenNEW.id:=NEW.id+1;raisenotice′returnNEW;when′UPDATE′thenNEW.id:=NEW.id+1;OLD.id:=OLD.id+1;raisenotice′returnNEW;when′DELETE′thenOLD.id:=OLD.id+1;raisenotice′returnOLD;endcase;end;
declare begin case TG_OP when 'INSERT' then NEW.id := NEW.id+1; raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW; return NEW; when 'UPDATE' then NEW.id := NEW.id+1; OLD.id := OLD.id+1; raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD; return NEW; when 'DELETE' then OLD.id := OLD.id+1; raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, OLD; return OLD; end case; end; language plpgsql; – 插入测试, 视图的最后一个被触发的instead of for each row触发器的返回值将对ROW_COUNT和RETURNING带来影响. 如下 : digoal=> insert into v_tbl values (1, ‘digoal’, now()) returning *; NOTICE: INSERT, tg0, INSTEAD OF, ROW, new:(2,digoal,”2013-03-11 08:49:22.983877”) NOTICE: INSERT, tg1, INSTEAD OF, ROW, new:(3,digoal,”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, ‘digoal’, 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, tg0, INSTEAD OF, ROW, old:(2,digoal,”2013-03-11 08:56:20.326402”) NOTICE: DELETE, tg1, INSTEAD OF, ROW, old:(2,digoal,”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
declarebegincaseTGOPwhen′INSERT′theninsertintotblvalues(NEW.∗);raisenotice′returnNEW;when′UPDATE′thendeletefromtblwheretbl.∗=OLD.∗;insertintotblvalues(NEW.∗);raisenotice′returnNEW;when′DELETE′thendeletefromtblwheretbl.∗=OLD.∗;raisenotice′returnOLD;endcase;end;
declare begin case TG_OP when 'INSERT' then insert into tbl values (NEW.*); raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW; return NEW; when 'UPDATE' then delete from tbl where tbl.* = OLD.*; insert into tbl values (NEW.*); raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD; return NEW; when 'DELETE' then delete from tbl where tbl.* = OLD.*; raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, 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还是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函数.
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
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 tgname,那么这个触发器只有当会话参数sessionreplicationrole=replica时,才会被触发.如果会话参数sessionreplicationrole=origin或者local,这个触发器将不会被触发.如果在表上面执行enablealwaystriggertg_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;
digoal (1 row) digoal=# \q – 会话中修改 : ocz@db-172-16-3-150-> psql psql (9.2.1) Type “help” for help. postgres=# show application_name;
psql (1 row) postgres=# set application_name=’abc’; SET postgres=# show 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
declarevappnametext;beginselectsettingintovappnamefrompgsettingswherename=′applicationname′;ifvappname=′digoal′thenreturnnull;endif;returnNEW;end;
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’;
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
declarebeginraisenotice′returnNEW;end;
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;
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
【小结】 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/