devbox
爱喝兽奶帝天荒
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

PostgreSQL触发器详解_触发pg是什么样的

作者：爱喝兽奶帝天荒 | 2024-06-29 19:59:52

赞

踩

触发pg是什么样的

1、触发器的定义

触发器声明了当执行一种特定类型的操作时数据库应该自动执行一个特殊的函数。触发器可以被附加到表(分区或不分区)、视图和外部表。

2、触发器的语法

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

3、触发器函数

创建触发器时需要指定这个触发器被触发时执行的函数, 这个函数就是触发器函数。触发器函数必须在触发器本身被创建之前被定义好。触发器函数必须被定义成一个没有参数的函数，并且返回类型为trigger(触发器函数通过一个特殊传递的TriggerData结构作为其输 入，而不是以普通函数参数的形式)。
TriggerData数据结构（include/commands/trigger.h） :

typedef struct TriggerData
{
        NodeTag         type;
        TriggerEvent tg_event;
        Relation        tg_relation;
        HeapTuple       tg_trigtuple;
        HeapTuple       tg_newtuple;
        Trigger    *tg_trigger;
        TupleTableSlot *tg_trigslot;
        TupleTableSlot *tg_newslot;
        Tuplestorestate *tg_oldtable;
        Tuplestorestate *tg_newtable;
} TriggerData;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

Trigger数据结构（include/utils/reltrigger.h）：

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;
        char       *tgoldtable;
        char       *tgnewtable;
} Trigger;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

触发器函数可以使用系统自带的过程语言(例如plpgsql, pltcl, plperl, plpython)来写, 也可以使用C来写.

触发器函数变量列表：
在这里插入图片描述

4、触发器分类

PostgreSQL 触发器支持行级、语句级触发器。对于一个每行的触发器，对于触发触 发器的语句所修改的每一行都会调用一次触发器函数。相反，一个每语句的触发器对于其触发语句只被调用一次，而不管该语句影响了多少行。支持insert,update,delete,truncate的方式触发。需要注意的是，truncate上的触发器只能在语句级，不能在行级。
在这里插入图片描述

5、触发器执行顺序

pg中允许在同一个表或视图上定义多个触发器，那么对于多个触发器它们的执行顺序又是什么样的呢？其执行顺序和触发器的类型有关。
表上各种触发器调用顺序：

  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

同种类型的触发器的触发顺序和名字有关，按照名字的顺序进行排序。

例1(表上测试)：
–创建测试表

bill=# create table bill (id int); 
CREATE TABLE

  • 1
  • 2

–创建触发器函数

bill=# create or replace function debug() returns trigger as $$  
bill$# declare   
bill$# begin    
bill$#   raise notice '%', TG_NAME;  
bill$#   return new;  
bill$# end;  
bill$# $$ language plpgsql; 
CREATE FUNCTION

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

–创建4种类型的触发器

bill=# create trigger tg1 before insert on bill for each statement execute procedure debug();
CREATE TRIGGER
bill=#  create trigger tg2 before insert on bill for each row execute procedure debug();
CREATE TRIGGER
bill=# create trigger tg3 after insert on bill for each row execute procedure debug(); 
CREATE TRIGGER
bill=# create trigger tg4 after insert on bill for each statement execute procedure debug();
CREATE TRIGGER

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

–创建4个同类型的触发器

bill=# create trigger tg01 before insert on bill for each statement execute procedure debug();
CREATE TRIGGER
bill=# create trigger tg02 before insert on bill for each row execute procedure debug(); 
CREATE TRIGGER
bill=# create trigger tg03 after insert on bill for each row execute procedure debug();
CREATE TRIGGER
bill=# create trigger tg04 after insert on bill for each statement execute procedure debug();
CREATE TRIGGER

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

–插入数据测试
可以看到每个触发器的执行顺序

bill=# insert into bill values (1); 
NOTICE:  tg01
NOTICE:  tg1
NOTICE:  tg02
NOTICE:  tg2
NOTICE:  tg03
NOTICE:  tg3
NOTICE:  tg04
NOTICE:  tg4
INSERT 0 1

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

例2(视图上测试)：
–创建测试视图

bill=# create view v_bill as select * from bill;  
CREATE VIEW

  • 1
  • 2

–创建触发器

bill=# create trigger tg1 before insert on v_bill for each statement execute procedure debug();  
CREATE TRIGGER
bill=# create trigger tg2 instead of insert on v_bill for each row execute procedure debug(); 
CREATE TRIGGER
bill=# create trigger tg3 after insert on v_bill for each statement execute procedure debug();
CREATE TRIGGER
bill=# create trigger tg01 before insert on v_bill for each statement execute procedure debug();
CREATE TRIGGER
bill=# create trigger tg02 instead of insert on v_bill for each row execute procedure debug(); 
CREATE TRIGGER
bill=# create trigger tg03 after insert on v_bill for each statement execute procedure debug();
CREATE TRIGGER

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

–插入数据测试
可以看到每个触发器的执行顺序

bill=# insert into v_bill values (2);
NOTICE:  tg01
NOTICE:  tg1
NOTICE:  tg02
NOTICE:  tg2
NOTICE:  tg03
NOTICE:  tg3
INSERT 0 1

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

6、触发器其它相关问题

问：
同一个触发器函数能否被多个触发器调用？
触发器函数的返回类型是什么？
触发器函数的返回值是否会影响下一个触发器函数或者被操作的行的数据?
NEW 或者OLD record修改后会带来什么影响? 哪些触发器函数的返回值没有意义?

答：
同一个触发器函数可以被多个触发器调用，上面两个例子中都有相关的使用。
触发器函数的返回值为空 或者是 表或视图对应的record类型。

–表触发器返回空举例：
–创建测试表

bill=# create table t1 (id int, info text, crt_time timestamp);  
CREATE TABLE

  • 1
  • 2

–创建触发器函数，返回空

bill=# create or replace function tg_t1() returns trigger as $$        bill$# declare  
bill$# begin  
bill$#   raise notice '%', TG_NAME;   
bill$#   return null;  
bill$# end;  
bill$# $$ language plpgsql; 
CREATE FUNCTION

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

–创建触发器

bill=# create trigger tg1 before insert on t1 for each statement execute procedure tg_t1();
CREATE TRIGGER
bill=# create trigger tg2 before insert on t1 for each row execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg3 after insert on t1 for each row execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg4 after insert on t1 for each statement execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg01 before insert on t1 for each statement execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg02 before insert on t1 for each row execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg03 after insert on t1 for each row execute procedure tg_t1();  
CREATE TRIGGER
bill=# create trigger tg04 after insert on t1 for each statement execute procedure tg_t1();
CREATE TRIGGER

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

–插入数据测试

bill=# insert into t1 values(1,'bill',now());  
NOTICE:  tg01
NOTICE:  tg1
NOTICE:  tg02
NOTICE:  tg04
NOTICE:  tg4
INSERT 0 0

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

可以看到因为tg01是before for each statement的触发器函数返回空, 不影响后续的触发器是否被调用。
但是需要注意在tg02这个第一个before for each row触发器调用的触发器函数返回空后, 后续的for each row触发器都没有被调用. 说明这个返回值传递给了下一个for each row触发器. 并且对后续的for each row触发器造成了影响。
所以对表的操作其实是发生在before for each row触发器tg02和第一个after for each row触发器之间，因为tg02返回为空，所以表中没有插入数据。
而after for each statement的触发器tg04返回空, 所以不影响后续的触发器是否被调用。

–查看表t1
因为tg02返回为空，所以确实没有数据

bill=# select * from t1;
 id | info | crt_time 
----+------+----------
(0 rows)

  • 1
  • 2
  • 3
  • 4

–表触发器返回record举例：
测试下NEW 或者OLD record修改后会带来什么影响?
经过上面的测试可以发现for each statement的返回值对行的值以及for each row的触发器没有影响，所以下面只测试for each row的触发器。

–创建测试表

bill=# create table t_ret(id int, info text, crt_time timestamp);
CREATE TABLE

  • 1
  • 2

–创建触发器函数，修改new并返回

bill=# create or replace function tg_t_ret() returns trigger as $$    bill$# declare  
bill$# begin  
bill$#   NEW.id := NEW.id+1;   
bill$#   raise notice '%, id:%', TG_NAME, NEW.id;   
bill$#   -- 修改NEW.id , 并返回修改后的NEW. 影响插入数据的并不是NEW变量本身,  而是return的值, 这个在后面将会有例子举证.  
bill$#   return NEW;  
bill$# end;  
bill$# $$ language plpgsql; 
CREATE FUNCTION

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

–创建触发器

bill=# create trigger tg1 before insert ON t_ret for each row execute procedure tg_t_ret(); 
CREATE TRIGGER

  • 1
  • 2

–插入数据

bill=# insert into t_ret values (1,'bill',now());          
NOTICE:  tg1, id:2
INSERT 0 1
bill=# select * from t_ret; 
 id | info |          crt_time          
----+------+----------------------------
  2 | bill | 2019-11-18 19:28:50.276372
(1 row)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

可以发现插入的数据是id=1，但是查询的结果确是id=2，说明before for each row的返回值对插入行这个动作造成了影响。

表级触发器小结：

insert触发器：

  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 触发器

  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 触发器：

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

–视图触发器返回空测试：

–创建测试表

bill=#  create table tbl (id int, info text, crt_time timestamp); 
CREATE TABLE

  • 1
  • 2

–创建视图

bill=# create view v_tbl as select * from tbl;  
CREATE VIEW

  • 1
  • 2

–创建触发器函数

bill=# create or replace function tg() returns trigger as $$  
bill$# declare  
bill$# begin  
bill$#   case TG_OP  
bill$#   when 'INSERT' then  
bill$#     raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW;  
bill$#   when 'UPDATE' then   
bill$#     raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD;  
bill$#   when 'DELETE' then  
bill$#     raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, OLD;  
bill$#   end case;  
bill$#   return null;  
bill$# end;  
bill$# $$ language plpgsql; 
CREATE FUNCTION

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

–创建触发器

bill=# create trigger tg0 instead of insert or update or delete on v_tbl for each row execute procedure tg();  
CREATE TRIGGER
bill=# create trigger tg1 instead of insert or update or delete on v_tbl for each row execute procedure tg(); 
CREATE TRIGGER

  • 1
  • 2
  • 3
  • 4

–插入数据测试
可以发现表中没有记录。因此可以得出结论：
before for each row触发器返回空, 将导致后面的for each row 触发器不被触发(注意for each statement不会跳过), 同时跳过对该行的操作。

bill=# insert into v_tbl values (1, 'bill', now()); 
NOTICE:  INSERT, tg0, INSTEAD OF, ROW, new:(1,bill,"2019-11-18 20:25:11.303009")
INSERT 0 0
bill=# select * from tbl;  
 id | info | crt_time 
----+------+----------
(0 rows)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

–视图触发器返回record测试
视图触发器返回record，那么NEW 或者OLD record修改后会带来什么影响?
–创建触发器函数

bill=# create or replace function tg() returns trigger as $$  
bill$# declare  
bill$# begin  
bill$#   case TG_OP  
bill$#   when 'INSERT' then  
bill$#     NEW.id := NEW.id+1;  
bill$#     raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW;  
bill$#     return NEW;  
bill$#   when 'UPDATE' then   
bill$#     NEW.id := NEW.id+1;  
bill$#     OLD.id := OLD.id+1;  
bill$#     raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD;  
bill$#     return NEW;  
bill$#   when 'DELETE' then  
bill$#     OLD.id := OLD.id+1;  
bill$#     raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, OLD;  
bill$#     return OLD;  
bill$#   end case;  
bill$# end;  
bill$# $$ language plpgsql;  
CREATE FUNCTION

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

–插入数据测试
视图的最后一个被触发的instead of for each row触发器的返回值将对记录数和RETURNING带来影响。可以发现最后一个触发器的returning变成了returning的输出。

bill=# insert into v_tbl values (1, 'bill', now()) returning *;       
NOTICE:  INSERT, tg0, INSTEAD OF, ROW, new:(2,bill,"2019-11-18 20:29:34.252504")
NOTICE:  INSERT, tg1, INSTEAD OF, ROW, new:(3,bill,"2019-11-18 20:29:34.252504")
 id | info |          crt_time          
----+------+----------------------------
  3 | bill | 2019-11-18 20:29:34.252504
(1 row)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

如果最后一个触发器的返回值不为空，则记录数会增加，否则不增加。

bill=# select * from tbl;
 id | info | crt_time 
----+------+----------
(0 rows)

  • 1
  • 2
  • 3
  • 4

下面测试delete操作对触发器影响：
–先向tbl表插入数据

bill=# insert into tbl values(1,'bill',now());
INSERT 0 1
bill=# select * from tbl;
 id | info |          crt_time          
----+------+----------------------------
  1 | bill | 2019-11-18 20:41:08.319237
(1 row)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

–删除数据
删除操作，触发器函数返回的值是OLD值，但是显然没有传递给第二个触发器。因为两次的OLD值都是2，如果传递给第二个触发器的话应该返回的OLD值是3.

bill=# delete from v_tbl where id=1 returning *; 
NOTICE:  DELETE, tg0, INSTEAD OF, ROW, old:(2,bill,"2019-11-18 20:41:08.319237")
NOTICE:  DELETE, tg1, INSTEAD OF, ROW, old:(2,bill,"2019-11-18 20:41:08.319237")
 id | info |          crt_time          
----+------+----------------------------
  1 | bill | 2019-11-18 20:41:08.319237
(1 row)

DELETE 1

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

视图级触发器小结：

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

总结：

哪些触发器函数的返回值没有意义?

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函数.

最后需要注意一点，不允许在系统表或者系统视图上创建触发器(backend/commands/trigger.c)：

bill=# 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:320

  • 1
  • 2
  • 3

在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/爱喝兽奶帝天荒/article/detail/770191
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号