PostgreSQL触发器总结

最近几个月忙于POC，很久没有时间研究东西了，今天趁着迁移一个触发器，顺便把触发器的知识捡起来~

参考资料：

https://www.postgresql.org/docs/12/plpgsql-trigger.html

 

概述：
触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE或DELETE操作之前或之后调用触发器。PostgreSQL支持两种类型的触发器，一种是数据行级触发器，另外一种是语句级触发器。对于数据行级的触发器，触发发触发器的语句每操作一个数据行，它就被执行一次。对于语句级的触发器，它只会被执行一次。

创建：
创建触发器以前，必须定义触发器使用的函数。这个函数不能有任何参数，它的返回值的类型必须是trigger。函数定义好以后，用命令CREATE TRIGGER创建触发器。多个触发器可以使用同一个函数。

参数：
定义触发器的时候，也可以为它指定参数（在CREATE TRIGGER命令中中指定）。系统提供了特殊的接口来访问这些参数。

执行顺序：
1）触发器按按执行的时间被分为before触发器和after触发器。语句级的before触发器在语句开始执行前被调用，语句级的after触发器在语句开始执行结束后被调用。
2）数据行级的before触发器在操作每个数据行以前被调用，数据行级的after触发器在操作每个数据行以后被调用。
3）如果同一表上同对同一个事件定义了多个触发器，这些触发器将按它们的名字的字母顺序被触发。
4）对于行级before触发器来说，前一个触发器返回的数据行作为后一个触发器的输入。如果任何一个行级before触发器返回NULL，后面的触发器将停止执行，触发触发器的INSERT/UPDATE/DELETE命令也不会被执行。

数据可见规则：
触发器在执行过程中，如果执行SQL命令访问触发器的父表中的数据，这些SQL命令遵循下面的数据可见规则，这些规则决定它们能否看见触发触发器的操作修改的表中的数据行：
1）语句级的before触发器在执行过程中，该语句的所有的对表中的数据的更新对它都不可见。
2）语句级的after触发器在执行过程中，该语句的所有的对表中的数据的更新对它都可见。
3）行级before触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的，但触发该触发器的数据行的更新操作的结果（插入、更新或删除）对它是不可见的。
4）行级after触发器在执行过程中，前面所有的已经被同一个命令处理的数据行对它是可见的。

返回值：
1）触发器函数必须返回一个NULL或者一个记录/数据行类型的变量，这个变量的结构必须与触发器作用的表的结构一样。
2）对于行级的BEFORE触发器，如果返回NULL，后面的触发器将不会被执行，触发这个触发器的INSERT/UPDATE/DELETE命令也不会执行。
3）如果行级的BEFORE触发器返回非空的值，则INSERT/UPDATE/DELETE命令继续执行。
4）对于UPDATE和INSERT操作触发的行级BEFORE触发器，如果它返回的数据行与更新以后的或被插入的数据行不相同，则以触发器返回的数据行作为新的更新好的数据行和被插入的数据行。
5）语句级的触发器的返回值和行级AFTER类型的行级触发器的返回值总是被忽略，没有任何意义，可以返回NULL。语句级的触发器应该返回NULL，否则会报错。

终止情况：
如果触发器在执行的过程中遇到或者发出了错误，触发触发器的操作将被终止。

用途、效率：
1）行级before触发器一般用于检查和修改将被插入和更新的数据。
2）行级after触发器一般用于将表中被更新的数据记录到其它的表中，或者检查与其它的表中的数据是否是一致的。
3）before触发器的执行效率比after触发器高，在before触发器和after触发器都能被使用的情况下，应该选择before触发器。

特殊触发器：
1）约束触发器：还有一种特殊的触发器叫约束触发器，这种触发器的执行时间可以被命令SET CONSTRAINTS控制。
2）事件触发器：
ddl_command_start - 一个DDL开始执行前被触发；
ddl_command_end - 一个DLL 执行完成后被触发；
sql_drop - 删除一个数据库对象前被触发；

触发器级联：
一个触发器在执行的过程中，如果执行了其它的SQL命令，可能会触发其它的触发器，这被称作触发器级联。
对于触发器级联的层次，系统没有任何限制，但触发器级联可能会调用前面已经执行过的触发器，从而引起死循环，系统不会检测这种现象，定义触发器的用户应该保证这种现象不会发生。

内置变量：
触发器在被调用时，系统会自动传递一些数据给它，这些数据包括触发触发器的事件类型（例如INSERT或UPDATE），对于行级触发器，还包括NEW数据行（对于INSERT和 UPDATE触发器）和OLD数据行（对于UPDATE和DELETE触发器）。每种可以用来书写触发器函数的语言都提供了取这些数据的方法。

当一个PL/pgSQL 函数作为一个触发器被调用时，系统自动在最外层的块创建一些特殊的变量。这些变量分别是：
1）NEW
数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。

2）OLD
数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。

3）TG_NAME
数据类型是name，它保存实际被调用的触发器的名字。

4）TG_WHEN
数据类型是text，根据触发器定义信息的不同，它的值是BEFORE 或AFTER。

5）TG_LEVEL
数据类型是text，根据触发器定义信息的不同，它的值是ROW或STATEMENT。

6）TG_OP
数据类型是text，它的值是INSERT、UPDATE或DELETE，表示触发触发器的操作类型。

7）TG_RELID
数据类型是oid，表示触发器作用的表的oid。

8）TG_RELNAME
数据类型是name，表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。

9）TG_TABLE_NAME
数据类型是name，表示触发器作用的表的名字。

10）TG_TABLE_SCHEMA
数据类型是name，表示触发器作用的表所在的模式。

11）TG_NARGS
数据类型是integer，表示CREATE TRIGGER命令传给触发器过程的参数的个数。

12）TG_ARGV[]
数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。TG_ARGV[0]表示第一个参数，TG_ARGV[1]表示第二个参数，以此类推。 如果下标小于0或大于等于tg_nargs，将会返回一个空值。

 

另附一个Oracle触发器迁移例子：

Oracle：

CREATE OR REPLACE TRIGGER JGSY_COMMON_TRIGGER
　　BEFORE INSERT OR DELETE OR UPDATE of update_time,edition
　　ON JGSY_COMMON FOR EACH ROW
 WHEN ((old.is_create is null or old.is_create != '1') and (new.is_create is null or new.is_create != '1')) DECLARE
    v_option_type varchar2(10);
    v_count number;
    v_record_id number;
BEGIN
    case
    when updating then
         v_option_type := '2';
    when inserting then
         v_option_type := '1';
    when deleting then
          v_option_type := '3';
    end case;

    --省略
END IF;
END;

 

PostgreSQL：

CREATE OR REPLACE TRIGGER JGSY_COMMON_TRIGGER
BEFORE INSERT OR DELETE OR UPDATE of update_time,edition
ON JGSY_COMMON FOR EACH ROW 
EXECUTE PROCEDURE JGSY_COMMON_TRIGGER_FUC();

CREATE OR REPLACE FUNCTION JGSY_COMMON_TRIGGER_FUC() RETURNS TRIGGER AS 
$BODY$
DECLARE
    v_option_type varchar2(10);
    v_count NUMERIC;
    v_record_id NUMERIC;
BEGIN
    case
    when TG_OP = 'UPDATE' then         
         IF (OLD.is_create = '1' or NEW.is_create = '1') THEN
         	--raise notice 'update is_create is not null or 1, do not execute trigger';
         	RETURN NULL;
         END IF;
     	v_option_type := '2';
    when TG_OP = 'INSERT' then         
         IF (NEW.is_create = '1') THEN
         	--raise notice 'insert is_create is not null or 1, do not execute trigger';
         	RETURN NULL;
         END IF;
         v_option_type := '1';
    when TG_OP = 'DELETE' THEN
    	  IF (OLD.is_create = '1') THEN
         	--raise notice 'DELETE is_create is not null or 1, do not execute trigger';
    	  	RETURN NULL;
          END IF;
          v_option_type := '3';          
    end case;
    --raise notice 'will execute trigger';
    --RETURN;
    --WHEN ((OLD.is_create is null or OLD.is_create != '1') and (NEW.is_create is null or NEW.is_create != '1')) 
	
   
END;
$BODY$
LANGUAGE plpgsql;