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

程序员文章站 2022-07-08 10:15:51

文章目录

触发器概念及特性
创建触发器的操作

触发器函数的特殊变量
创建简单的事件触发器
创建一个update触发器

触发器概念及特性

查看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”，如果没有写，将导致出错
行级的触发器会随着返回值和类型的不同，而对触发触发器操作造成不同的影响。

对于before 和 instead of 行级触发器其返回值为NULL时：触发该触发器的增加、更新和删除操作不会被执行。

行级before触发器返回值非NULL时：这三者可以向下执行。

对于插入和更新操作引起的before触发器来说，返回的行非空时（且数据行发生变化）该行将成为被插入的行或将要更新的行。

行级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 函数当作触发器函数调用的时候，系统会在顶层的声明段里自动创建几个 特殊变量，比如下面的这些变量：

NEW：该变量为插入和更新操作触发的行级触发器中存储的新的数据行，数据类型是 RECORD。在语句级别的触发器里此变量没有分配，删除操作触发的行级触发器中此变量也没有分配
OLD：该变量为更新和删除操作触发的行级触发器中存储的旧数据行，数据类型是 RECORD。在语句级别的触发器里此变量也没有分配，同理插入操作触发的行级触发器中此变量也没有分配
TG_NAME：数据类型是 name，该变量包含实际触发的触发器名字
TG_WHEN: 内容为 BEFORE 或 AFTER 的字符串，用于指定是 BEFORE 触发器还是 AFTER 触发器
TG_LEVEL: 内容为 ROW 或 STATEMENT 的字符串用于指定是语句级触发器还是行级触发器
TG_OP: 内容为 INSERT、UPDATE、DELETE、TRUNCATE 之一的字符串，用于指定 DML 语句的类型
TG_RELID: 触发器所在表的 OID
TG_TABLE_NAME: 触发器所在表的名称
TG_TABLE_SCHEMA: 触发器所在表的模式
TG_NARGS: 在 CREATE TRIGGER 语句里面赋予触发器过程的参数个数
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=#

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

使用insert语句执行新增插入数据
插入完成触发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语句插入记录验证触发器是否生效：
PostgreSQL的学习心得和知识总结（十二）|数据库触发器使用说明及特性总结

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

相关标签： PostgreSQL的学习心得和知识总结

上一篇：浅析InnoDB索引结构

下一篇： Oracle 管道函数详解（pipelined）

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

文章目录

触发器概念及特性

创建触发器的操作

触发器函数的特殊变量

创建简单的事件触发器

创建一个update触发器

有关php GetFiletype()函数的文章推荐10篇

Python框架Flask的基本数据库操作方法分析

《数据库系统概论》复习笔记

mysql-请问：用java代码把不同的excel形式加载到MySQL数据库中（循环判断空就停止）？谢谢！

Python中的ctime()方法使用教程

数据库多个事物的并发问题

js-关于html标签转义的问题

PHP扩展开发-数组的使用以及HashTable简介

Photoshop 制作一张简洁的情人节贺卡

验证国内手机号与座机号的正则表达式