【openGauss如何通过pg_trigger.tgtype获取触发器的各种触发条件-每日运维

前言

最近有客户反馈兼容的dba_triggers视图中，同一个触发器的trigger_event被拆成了多行，和ORACLE中表现不一致，于是我进行了一些分析，发现是在其引用的information_schema.triggers视图中就已经拆开成了INSERT/DELETE/UPDATE，但是这些属性都是通过tgtype这一个int2整型的字段获取的，甚至连before/after/instead of/row/statement 等都是通过这一个字段。一个值存多种信息，这在ORACLE的数据字典视图里很常见，无非就是按二进制位来判断，于是我尝试自己猜一猜，看能不能从这个数字中识别出规律。

测试和分析

先建一堆测试触发器

create schema test1; create schema test2;


CREATE TABLE test1.test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE OR REPLACE FUNCTION test1.tri_test_func() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--before insert/update row

CREATE TRIGGER test_trigger

           BEFORE insert or update ON test1.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func();
CREATE TABLE test2.test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE OR REPLACE FUNCTION test2.tri_test_func() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--不同schema下的同名触发器

CREATE TRIGGER test_trigger

           BEFORE insert or update ON test2.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test2.tri_test_func();
CREATE OR REPLACE FUNCTION test1.tri_test_func1() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
-- before insert row

CREATE TRIGGER test_trigger1

           BEFORE insert  ON test1.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func1();
CREATE OR REPLACE FUNCTION test1.tri_test_func2() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--before insert/delete row

CREATE TRIGGER test_trigger2

           BEFORE insert or DELETE ON test1.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func2();
CREATE OR REPLACE FUNCTION test1.tri_test_func3() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--after insert/delete/update row

CREATE TRIGGER test_trigger3

           AFTER insert or delete or UPDATE ON test1.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func3();
CREATE OR REPLACE FUNCTION test1.tri_test_func4() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
create view test1.test_trigger_src_tbl_V as select * from test1.test_trigger_src_tbl;
--instead delete row

CREATE TRIGGER test_trigger4

           instead OF DELETE ON test1.test_trigger_src_tbl_V

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func4();
CREATE OR REPLACE FUNCTION test1.tri_test_func5() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--before truncate statement

CREATE TRIGGER test_trigger5

           before truncate ON test1.test_trigger_src_tbl

           FOR EACH STATEMENT

           EXECUTE PROCEDURE test1.tri_test_func5();
CREATE OR REPLACE FUNCTION test1.tri_test_func6() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--after delete row

CREATE TRIGGER test_trigger6

           AFTER delete  ON test1.test_trigger_src_tbl

           FOR EACH ROW

           EXECUTE PROCEDURE test1.tri_test_func6();
CREATE OR REPLACE FUNCTION test1.tri_test_func7() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;
--after truncate statement

CREATE TRIGGER test_trigger7

           AFTER truncate ON test1.test_trigger_src_tbl

           FOR EACH STATEMENT

           EXECUTE PROCEDURE test1.tri_test_func7();
 CREATE OR REPLACE FUNCTION test1.tri_test_func8() RETURNS TRIGGER AS

           $$

           DECLARE

           BEGIN

                   INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);

                   RETURN NEW;

           END

           $$ LANGUAGE PLPGSQL;

--after update/delete row CREATE TRIGGER test_trigger8 AFTER update or delete ON test1.test_trigger_src_tbl FOR EACH ROW EXECUTE PROCEDURE test1.tri_test_func8();

然后查询pg_trigger表，并将tgtype转换成二进制数值显示（注意这里int2无法直接转换成bit类型)

select tgname,tgtype,tgtype::int4::bit(8) from pg_trigger;

tgname	tgtype	tgtype	实际类型
test_trigger	23	00010111	before insert update row
test_trigger	23	00010111	before insert update
test_trigger1	7	00000111	before insert row
test_trigger2	15	00001111	before insert/delete row
test_trigger3	29	00011101	after insert/delete/update row
test_trigger4	73	01001001	instead delete row
test_trigger5	34	00100010	before truncate statement
test_trigger6	9	00001001	after delete row
test_trigger7	32	00100000	after truncate statement
test_trigger8	25	00011001	after update/delete row

得到这个信息，小学二年级都能看出规律。我们在表格中转置一下看看

tgname	test_trigger	test_trigger	test_trigger1	test_trigger2	test_trigger3	test_trigger4	test_trigger5	test_trigger6	test_trigger7	test_trigger8
tgtype	23	23	7	15	29	73	34	9	32	25
7	0	0	0	0	0	0	0	0	0	0
6	0	0	0	0	0	1	0	0	0	0
5	0	0	0	0	0	0	1	0	1	0
4	1	1	0	0	1	0	0	0	0	1
3	0	0	0	1	1	1	0	1	0	1
2	1	1	1	1	1	0	0	0	0	0
1	1	1	1	1	0	0	1	0	0	0
0	1	1	1	1	1	1	0	1	0	1
实际类型	before insert update row	before insert update	before insert row	before insert/delete row	after insert/delete/update row	instead delete row	before truncate statement	after delete row	after truncate statement	after update/delete row

然后很容易就能对比得到每个二进制位所表示的含义

第几位	含义
7	无用位
6	是否insead of
5	是否truncate
4	是否update
3	是否delete
2	是否insert
1	是否before（不是before就是after)
0	是否row（不是row就是statement）

以上都是纯用SQL查询比较猜出来的，虽然过程也比较简单，但还是有点费时间。但实际上，如果能看懂C语言源码，一眼就能知道应该怎么去解析tgtype
openGauss-server\src\include\catalog\pg_trigger.h

/* Bits within tgtype */ #define TRIGGER_TYPE_ROW (1

【openGauss如何通过pg_trigger.tgtype获取触发器的各种触发条件

前言

测试和分析

相关文章

发布评论取消回复

三掌柜

百度搜索：蓝易云 – CentOS7编译生成openssl和openssh RPM包教程

【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统

蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践

【openGauss如何通过pg_trigger.tgtype获取触发器的各种触发条件

前言

测试和分析

相关文章

发布评论 取消回复

三掌柜

百度搜索：蓝易云 – CentOS7编译生成openssl和openssh RPM包教程

【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统

蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践

发布评论取消回复