PostgreSQL的学习心得和知识总结（十二）|数据库触发器使用说明及特性总结

触发器概念及特性

查看PostgreSQL当前数据库所有的触发器：

postgres=# SELECT * FROM pg_trigger;
 oid | tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable 
-----+---------+--------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+---------
(0 rows)

postgres=#

那么触发器是什么？以及触发器存在的作用or意义是什么？

它是一种由事件自动触发 并 执行的特殊存储过程，而所谓的事件可以是对一个表进行 增加、更新和删除等操作。自动触发之后是开始了一组动作或数据库回调函数，它们在事件之前or之后触发了：做一些验证输入数据、执行业务规则和保持审计跟踪等任务。一言以蔽之：用于加强数据的完整性约束和业务规则上的约束等，无须重复多次的SQL命令就能达成一连串数据的同步处理操作。

---

触发器的分类划分如下：

在PostgreSQL数据库中，支持两种类型的触发器：数据行级触发器 和 语句级触发器。对于数据行级的触发器，触发触发器的语句每操作一个数据行，它就被执行一次。而对于语句级的触发器，它仅仅只会被执行一次。

而按照触发器执行的时间来划分，可以被分为before触发器 和 after触发器。语句级的before触发器在语句开始执行前被调用，语句级的after触发器在语句开始执行结束后被调用。数据行级的before触发器在操作每个数据行以前被调用，数据行级的after触发器在操作每个数据行以后被调用（但是它会在任何语句级的 after触发器被触发之前触发）。

数据行级的before触发器一般用于检查和修改将被插入和更新的数据

数据行级的after触发器则一般用于将表中被更新的数据记录到其它的表中，或者检查与其它的表中的数据是否是一致的

before触发器的执行效率比after高：在二者都能被使用的情况下，应该选择前者

触发器的重要特点如下：

• PostgreSQL在以下情况下执行/调用触发器：在尝试操作之前(在检查约束并尝试插入、更新和删除之前) 。or 在操作完成之后(在检查约束并且插入、更新和删除完成后)
• 被标记为FOR EACH ROWS的触发器都会在每一行被改动操作的时候调用；标记为FOR EACH STATEMENT的触发器则只对任何给定的操作执行一次，而不论它修改多少行
• 触发器函数定义好以后（先函数），用命令CREATE TRIGGER ……创建触发器（后触发器）
• 可以为同一个事件定义多个同一类型的触发器，但条件是按名称(按字母顺序)进行触发。同样多个触发器可以使用同一个触发器函数
• 当与触发器相关联的表被删除时，触发器也就被自动删除了

---

触发器函数的重要特点如下：

上面也说了，触发器的创建在触发器函数之后。创建触发器的第一步就是：先为触发器建一个执行函数，此函数的返回类型为触发器类型 trigger；然后即可创建相应的触发器。

• 语句级的触发器应该返回NULL，即必须显式地在触发器函数中写上 “RETURN NULL”，如果没有写，将导致出错
• 行级的触发器会随着返回值 和 类型的不同，而对触发触发器操作造成不同的影响。

1. 对于before 和 instead of 行级触发器 其返回值为NULL时：触发该触发器的增加、更新和删除操作不会被执行。
2. 行级before触发器返回值非NULL时：这三者可以向下执行。
3. 对于插入 和 更新操作引起的before触发器来说，返回的行非空时（且数据行发生变化）该行将成为被插入的行或将要更新的行。
4. 行级after触发器的返回值没啥用，被忽略

注：我们上面也说了，如果在同一表上对同一个事件定义了多个触发器 然后按照名字顺序触发。这里需要注意一点：对于行级before触发器来说：前一个触发器返回的数据行作为后一个触发器的输入。如果任何一个行级before触发器返回NULL，则后面的触发器将停止执行，触发触发器的增加、更新和删除操作也不会被执行。

创建触发器的操作

CREATE TRIGGER语句用于在PostgreSQL表中创建一个新的触发器。 当该表发生特定事件(即增加、更新和删除操作)时，触发器将被触发！ 其语法格式如下：

# event_name可以是增 删 改和截断数据库操作上提到的 表名，可以在表名后加上 FOR EACH ROW
CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
ON table_name  
[  
	# 触发器逻辑处理 
];

CREATE  TRIGGER trigger_name AFTER INSERT ON column_name  
ON table_name  
[  
	# 触发器逻辑处理
];

注：在创建事件触发器之前，必须先创建触发器函数，事件触发器函数的返回类型是event_trigger，这个与普通触发器函数的返回类型 （trigger）是不一样的。

触发器函数的特殊变量

当把一个 PL/pgSQL 函数当作触发器函数调用的时候，系统会在顶层的声明段里自动创建几个 特殊变量，比如下面的这些变量：

1. NEW：该变量为 插入和更新 操作触发的行级触发器中存储的新的数据行，数据类型是 RECORD。在语句级别的触发器里此变量没有分配，删除操作触发的行级触发器中此变量也没有分配
2. OLD：该变量为 更新和删除 操作触发的行级触发器中存储的旧数据行，数据类型是 RECORD。在语句级别的触发器里此变量也没有分配， 同理插入操作触发的行级触发器中此变量也没有分配
3. TG_NAME：数据类型是 name，该变量包含实际触发的触发器名字
4. TG_WHEN: 内容为 BEFORE 或 AFTER 的字符串，用于指定是 BEFORE 触发器还是 AFTER 触发器
5. TG_LEVEL: 内容为 ROW 或 STATEMENT 的字符串用于指定是语句级触发器还是行级触发器
6. TG_OP: 内容为 INSERT、UPDATE、DELETE、TRUNCATE 之一的字符串，用于指定 DML 语句的类型
7. TG_RELID: 触发器所在表的 OID
8. TG_TABLE_NAME: 触发器所在表的名称
9. TG_TABLE_SCHEMA: 触发器所在表的模式
10. TG_NARGS: 在 CREATE TRIGGER 语句里面赋予触发器过程的参数个数
11. TG_ARGV[]: 为 text 类型的一个数组，存放 CREATE TRIGGER 语句里的各个参数。下标从0开始，TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。 如果下标小于0或大于等于tg_nargs，将会返回一个空值。

创建简单的事件触发器

这里的事件触发器不是我们的重点，创建事件触发器的语法如下：

CREATE EVENT TRIGGER 
ON event
[ WHEN filter_variable IN (filter_value [,...]) [ and ...]]
EXECUTE PROCEDURE function_name()

下面给出一个实例：（禁止所有 DDL 语句的例子）

postgres=# \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | student | table | uxdb
(1 row)

postgres=# CREATE FUNCTION abort_any_command() returns event_trigger language plpgsql AS $$
postgres$# BEGIN
postgres$#     RAISE EXCEPTION 'command % is disabled', tg_tag;
postgres$# END;
postgres$# $$;
CREATE FUNCTION
postgres=# 
postgres=# CREATE EVENT TRIGGER abort_DDL ON DDL_command_start EXECUTE PROCEDURE abort_any_command();
CREATE EVENT TRIGGER
postgres=# 
# 解释一下创建事件触发器：
# 1、TG_EVENT的值: 为 "ddl_command_start"、"ddl_command_end"、"sql_drop" 之一
# 2、TG_TAG: 指的是具体的哪种 DDL 操作，如 "CREATE TABLE"、"DROP TABLE" 等。

postgres=# \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | student | table | uxdb
(1 row)

postgres=# drop table student ;
2020-07-16 16:06:06.637 CST [48510] ERROR:  command DROP TABLE is disabled
2020-07-16 16:06:06.637 CST [48510] CONTEXT:  PL/pgSQL function abort_any_command() line 3 at RAISE
2020-07-16 16:06:06.637 CST [48510] STATEMENT:  drop table student ;
ERROR:  command DROP TABLE is disabled
CONTEXT:  PL/pgSQL function abort_any_command() line 3 at RAISE
postgres=#
postgres=# \d student 
                     Table "public.student"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 name   | character varying(16) |           |          | 
 score  | integer               |           |          | 

postgres=# insert into student values ('hello',99);
INSERT 0 1
postgres=# create table test (id int);
2020-07-16 16:10:15.759 CST [48510] ERROR:  command CREATE TABLE is disabled
2020-07-16 16:10:15.759 CST [48510] CONTEXT:  PL/pgSQL function abort_any_command() line 3 at RAISE
2020-07-16 16:10:15.759 CST [48510] STATEMENT:  create table test (id int);
ERROR:  command CREATE TABLE is disabled
CONTEXT:  PL/pgSQL function abort_any_command() line 3 at RAISE
postgres=#

此时执行的DDL命令（其他的不受影响）将都会报错！

如果想再允许 DDL 操作，可以禁止事件触发器 如下：

postgres=# ALTER EVENT TRIGGER abort_ddl DISABLE;
ALTER EVENT TRIGGER
postgres=# 
postgres=# create table test (id int);
CREATE TABLE
postgres=# 

#--------------------------------------#
postgres=#  drop event trigger abort_ddl; #删除
DROP EVENT TRIGGER
postgres=#

虽然上面删除了触发器，但触发器的函数不会被删除。当表删除时，表上的触发器也会被删除。这里我们可以使用 DROP FUNCTION fun_name 直接删除触发器函数：

postgres=# SELECT pg_proc.proname,pg_type.typname,pg_proc.pronargs,pg_proc.oid FROM pg_proc JOIN pg_type  ON (pg_proc.prorettype = pg_type.oid) where  pg_type.typname != 'void' AND pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public');
       proname       |    typname    | pronargs |  oid  
---------------------+---------------+----------+-------
 make_pair           | mytype        |        3 | 16417
 make_pair_list      | mytype        |        3 | 16418
 make_kv             | mydictype     |        2 | 16422
 make_object         | mydictype     |        2 | 16423
 make_object2        | mydictype     |        2 | 16427
 myout               | record        |        0 | 16430
 myinout             | record        |        2 | 16431
 getsetfromsequence  | mydictype     |        1 | 16432
 getsetfromsequence2 | mydictype     |        1 | 16433
 getsetfromsequence3 | mydictype     |        1 | 16436
 getsetfromiterator  | mydictype     |        1 | 16437
 getsetfromyield     | mydictype     |        1 | 16438
 tabela_be_i_tg_fx   | trigger       |        0 | 16439
 abort_any_command   | event_trigger |        0 | 16440
(14 rows)

postgres=# select prosrc,proname from pg_proc where proname='abort_any_command';
                        prosrc                        |      proname      
------------------------------------------------------+-------------------
                                                     +| abort_any_command
 BEGIN                                               +| 
     RAISE EXCEPTION 'command % is disabled', tg_tag;+| 
 END;                                                +| 
                                                      | 
(1 row)

postgres=# DROP FUNCTION abort_any_command
postgres-# ;
DROP FUNCTION
postgres=#

创建一个update触发器

我们这里就创建一个update触发器来演示一下 触发器完整的创建过程：

先模拟一个问题场景：我这里有一个test表 （两个字段，第二个字段是时间戳，默认值为空）

postgres=# create table test (id int,time timestamp default NULL);
CREATE TABLE
postgres=# insert into test values (1);
INSERT 0 1
postgres=# select * from test ;
 id | time 
----+------
  1 | 
(1 row)

postgres=#

如上，我在插入这一个记录之后，时间戳字段是空的！对这个过程分解如下：

1. 使用insert语句执行新增插入数据
2. 插入完成触发update语句，针对插入的记录，将此刻的时间（now()）赋值给time字段

创建一个update触发器函数，用于修改数据：

postgres=# CREATE OR REPLACE FUNCTION update_time() RETURNS trigger AS $BODY$ 
BEGIN  
    update test set time=now() where id=NEW.ID;
    return NEW;
END;
$BODY$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 

注：这里面有个NEW对象，表示insert记录record，注意where条件。

之后创建触发器TRIGGER，表示在insert之后触发update_time函数/存储过程：

postgres=# 
postgres=# CREATE TRIGGER update_trigger AFTER INSERT on test for EACH ROW EXECUTE PROCEDURE update_time();
CREATE TRIGGER
postgres=# 

最后我们是用insert语句插入记录验证触发器是否生效：

本文地址：https://blog.csdn.net/weixin_43949535/article/details/107381916