【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();
