Postgresql 触发器实例及相关知识了解

执行顺序

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，将会返回一个空值。

触发器实例

-------------------------------------查询触发器---------------------------------------------

select * from infORMation_schema.triggers;

-------------------------------------表结构准备--------------------------------------------

drop table if exists xh_yw.xh_trigger_records_tb;

create table xh_yw.xh_trigger_records_tb(
 ID SERIAL PRIMARY KEY      NOT NULL,
 -- 数据类型： 0:ORG     1:USER
 DATA_TYPE  INT    NOT null,
 -- 业务类型： 0:Redis   1:POST
 OPT_TYPE INT    NOT null,
 -- 数据操作类型： 0:INSERT    1:UPDATE     2:DELETE
 DB_OPT_TYPE INT    NOT null,
 -- 新(原)组织机构ID
 I_ORGID INT    NOT NULL,
 -- 新(原)组织机构编码
 OLD_ORGBH VARCHAR(50) default null,
 -- 新组织机构编码
 NEW_ORGBH VARCHAR(50) default null,
 -- 用户ID
 I_USERID INT    NOT NULL,
 -- 操作时间
 OPT_TIME TIMESTAMP  NOT NULL
);

CREATE SEQUENCE xh_yw.xh_trigger_records_tb_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;


-- ----------------------------------创建组织机构表和用户表触发器函数-------------------------------------------


---组织机构插入函数----insert
CREATE OR REPLACE FUNCTION orgInsertFunc() RETURNS TRIGGER AS $$
        BEGIN
             insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (0,0,0,new.I_ORGID,new.C_ORGBH,new.C_ORGBH,-1,now());
        RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;

---组织机构更新函数----update
CREATE OR REPLACE FUNCTION orgUpdateFunc() RETURNS TRIGGER AS $$
        BEGIN
             IF NEW.C_ORGBH <> OLD.C_ORGBH THEN
				        insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (0,1,1,old.I_ORGID,old.C_ORGBH,new.C_ORGBH,-1,now());
                update xh_ht.fs_yw_base_user set C_ORGBH = new.C_ORGBH where I_ORGID = old.I_ORGID;
             END IF;
        RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;


---组织机构删除函数----delete
CREATE OR REPLACE FUNCTION orgDeleteFunc() RETURNS TRIGGER AS $$
        BEGIN
             insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (0,0,2,old.I_ORGID,old.C_ORGBH,old.C_ORGBH,-1,now());
        RETURN NULL;
        END;
    $$ LANGUAGE plpgsql;

---用户插入函数----insert
CREATE OR REPLACE FUNCTION userInsertFunc() RETURNS TRIGGER AS $$
        BEGIN
             insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (1,0,0,new.I_ORGID,new.C_ORGBH,new.C_ORGBH,new.I_USERID,now());
        RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;

---用户更新函数----update
CREATE OR REPLACE FUNCTION userUpdateFunc() RETURNS TRIGGER AS $$
        BEGIN
             IF NEW.I_ORGID <> OLD.I_ORGID THEN
				          insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (1,1,1,new.I_ORGID,new.C_ORGBH,new.C_ORGBH,old.I_USERID,now());
             ELSE
 				        IF NEW.C_ORGBH <> OLD.C_ORGBH THEN
					        insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (1,1,1,old.I_ORGID,old.C_ORGBH,new.C_ORGBH,old.I_USERID,now());
              	END IF;
			      END IF;
        RETURN NEW;
        END;
    $$ LANGUAGE plpgsql;


---用户删除函数----delete
CREATE OR REPLACE FUNCTION userDeleteFunc() RETURNS TRIGGER AS $$
        BEGIN
             insert into xh_yw.xh_trigger_records_tb(DATA_TYPE,OPT_TYPE,DB_OPT_TYPE,I_ORGID,OLD_ORGBH,NEW_ORGBH,I_USERID,OPT_TIME)
                                    values (1,0,2,old.I_ORGID,old.C_ORGBH,old.C_ORGBH,old.I_USERID,now());
        RETURN NULL;
        END;
    $$ LANGUAGE plpgsql;

-- ---------------------------------删除组织机构表和用户表触发器------------------------------------------

DROP TRIGGER org_insert_trigger on xh_ht.fs_yw_base_org;
DROP TRIGGER org_update_trigger on xh_ht.fs_yw_base_org;
DROP TRIGGER org_delete_trigger on xh_ht.fs_yw_base_org;

DROP TRIGGER user_insert_trigger on xh_ht.fs_yw_base_user;
DROP TRIGGER user_update_trigger on xh_ht.fs_yw_base_user;
DROP TRIGGER user_delete_trigger on xh_ht.fs_yw_base_user;

-- ---------------------------------创建组织机构表和用户表触发器------------------------------------------

CREATE TRIGGER org_insert_trigger AFTER INSERT ON xh_ht.fs_yw_base_org  FOR EACH ROW EXECUTE PROCEDURE orgInsertFunc();

CREATE TRIGGER org_update_trigger AFTER UPDATE ON xh_ht.fs_yw_base_org  FOR EACH ROW EXECUTE PROCEDURE orgUpdateFunc();

CREATE TRIGGER org_delete_trigger AFTER DELETE ON xh_ht.fs_yw_base_org  FOR EACH ROW EXECUTE PROCEDURE orgDeleteFunc();



CREATE TRIGGER user_insert_trigger AFTER INSERT ON xh_ht.fs_yw_base_user  FOR EACH ROW EXECUTE PROCEDURE userInsertFunc();

CREATE TRIGGER user_update_trigger AFTER UPDATE ON xh_ht.fs_yw_base_user  FOR EACH ROW EXECUTE PROCEDURE userUpdateFunc();

CREATE TRIGGER user_delete_trigger AFTER DELETE ON xh_ht.fs_yw_base_user  FOR EACH ROW EXECUTE PROCEDURE userDeleteFunc();

参考阅读： https://www.cnblogs.com/ryanzheng/p/9902951.html

参考示例： Https://blog.csdn.net/NIOqnw/article/details/84633181