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

最新推荐文章于 2024-07-09 11:17:09 发布
最新推荐文章于 2024-07-09 11:17:09 发布
阅读量421 收藏
点赞数
分类专栏： openGauss 数据库 文章标签： 数据库
CC BY-NC-SA 3.0
本文链接：https://blog.csdn.net/wwwwwwgame/article/details/134746677
版权

前言

最近有客户反馈兼容的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;
tgnametgtypetgtype实际类型
test_trigger2300010111before insert update row
test_trigger2300010111before insert update
test_trigger1700000111before insert row
test_trigger21500001111before insert/delete row
test_trigger32900011101after insert/delete/update row
test_trigger47301001001instead delete row
test_trigger53400100010before truncate statement
test_trigger6900001001after delete row
test_trigger73200100000after truncate statement
test_trigger82500011001after update/delete row

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

tgnametest_triggertest_triggertest_trigger1test_trigger2test_trigger3test_trigger4test_trigger5test_trigger6test_trigger7test_trigger8
tgtype232371529733493225
70000000000
60000010000
50000001010
41100100001
30001110101
21111100000
11111001000
01111110101
实际类型before insert update rowbefore insert updatebefore insert rowbefore insert/delete rowafter insert/delete/update rowinstead delete rowbefore truncate statementafter delete rowafter truncate statementafter 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 << 0)
#define TRIGGER_TYPE_BEFORE             (1 << 1)
#define TRIGGER_TYPE_INSERT             (1 << 2)
#define TRIGGER_TYPE_DELETE             (1 << 3)
#define TRIGGER_TYPE_UPDATE             (1 << 4)
#define TRIGGER_TYPE_TRUNCATE           (1 << 5)
#define TRIGGER_TYPE_INSTEAD            (1 << 6)

另外，为什么明明有INSERT/DELETE/UPDATE/TRUNCATE四种，但information_schema.triggers里为什么没有truncate的呢？其实在视图里，明确有写

         -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
         -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
         (VALUES (4, 'INSERT'),
                 (8, 'DELETE'),
                 (16, 'UPDATE')) AS em (num, text)

我们故意省略TRIGGER_TYPE_TRUNCATE

至于原作者为什么故意省略，这个就猜不到了。
知道以上规则后，我们可以尝试自己写一个dba_trigger视图，这里对event提供两种写法

case when tgtype&32<>0 then 'TRUNCATE'
     when tgtype&6<>0 and tgtype&16<>0 and tgtype&8<>0 then 'INSERT OR UPDATE OR DELETE'
     when tgtype&6<>0 and tgtype&16<>0 and tgtype&8=0 then 'INSERT OR UPDATE'
     when tgtype&6<>0 and tgtype&16=0 and tgtype&8=0 then 'INSERT'
     when tgtype&6=0 and tgtype&16<>0 and tgtype&8<>0 then 'UPDATE OR DELETE'
     when tgtype&6=0 and tgtype&16<>0 and tgtype&8=0 then 'UPDATE'
     when tgtype&6=0 and tgtype&16=0 and tgtype&8<>0 then 'DELETE'
     when tgtype&6<>0 and tgtype&16=0 and tgtype&8<>0 then 'INSERT OR DELETE'
end

case substring(tgtype::int4::bit(8) from 3 for 5)
      when B'01110' then 'INSERT OR UPDATE OR DELETE'
      when B'01011' then 'INSERT OR UPDATE'
      when B'00011' then 'INSERT'
      when B'01100' then 'UPDATE OR DELETE'
      when B'00100' then 'DELETE'
      when B'00111' then 'INSERT OR DELETE'
      when B'10001' then 'TRUNCATE'
end

视图代码地址：

https://gitee.com/enmotech/cmpat-tools/blob/master/Oracle_Views.sql

结尾

开头有提到，在ORACLE中也经常这样处理属性值，比如以下就是一个oracle的user_triggers视图的一段节选，用这一个property字段表示了很多种属性

decode(bitand(t.property, 8192),
       8192, decode(bitand(t.property, 131072),
                    131072, 'REVERSE', 'FORWARD'), 'NO'),
decode(bitand(t.property, 16384),
             16384, 'YES', 'NO'),
decode(bitand(t.property, 32768),
             32768, 'YES', 'NO'),

好的设计都是相通的，虽然这种方式不利于直接用SQL从数据字典基表中查询明确的属性值，但是能节省很多存储空间，并且在内存中直接判断二进制会更快比字符串更快。

DarkAthena
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
openGauss学习笔记-43 openGauss 高级数据管理-事件触发器
超哥的博客
08-18 529
触发器会在指定的ddl事件发生时自动执行函数。目前事件触发器仅在PG兼容模式下可用。
【openGauss笔记】触发器
ZhShy
10-15 2859
在warehouse表上创建一个完整的触发器，触发器的工作是在wh_log表中记录DELETE/UPDATE/INSERT操作的具体信息。针对触发对象的触发事件一旦发生，就会激活触发器。同一个触发器可以指定多个触发事件，每个事件发生时都能激活触发器来执行触发器的动作。触发器是对应用动作的响应机制，当应用对一个对象发起DML操作时，就会产生一个。如果该对象上拥有该事件对应的触发器，那么就会检查触发器的。元组触发器可以将更新前后的值嵌入到触发器的触发条件里。：针对SQL语句级的触发器。：针对元组级的触发器。
openGauss插件使用指南:SQL语法—CREATE TRIGGER（2）
最新发布
weixin_53596073的博客
07-09 226
【代码】openGauss插件使用指南:SQL语法—CREATE TRIGGER（2）
postgres pg_trigger
kwame211的博客
11-23 349
pg_trigger The catalog pg_trigger stores triggers on tables and views. See CREATE TRIGGER for more information. Table 47-45. pg_trigger Columns Name Type References Description oid ...
PostgreSQL中的触发器
HighGO
04-05 1909
作者：瀚高PG实验室（HighgoPGLab） 丹心明月 注：本文章主要翻译自《PostgreSQL 13.0 Documentation》第三十八章 本章介绍编写函数触发器的相关信息。触发器函数可使用大部分可用的过程语言编写，包括PL/pgSQL（参见第42章），PL/Tcl（参见第43章），PL/Perl（参见第44章），和PL/Python（参见第45章）。阅读完本章后，可根据自己的喜好，到相应章节查看对应语言编写触发器的具体细节。 也可以使用C语言编写触发器，不过使用过程语言更简...
无涯教程-PostgreSQL - Triggers(触发器)
无涯教程
12-27 618
PostgreSQL 触发器是数据库回调函数，当发生指定的数据库事件时，它们会自动执行。
PostgreSQL教程：枚举类型
学亮编程手记
09-16 267
可以声明枚举类型作为表中的字段类型，这样可以无形的给表字段追加诡异的规范。枚举类型MySQL也支持，只是没怎么用，PGSQL同样支持这种数据类型。
postgresql之触发器
weixin_44847119的博客
08-28 8252
一，前人总结 1.触发器在指定的数据库事件发生时调用 2.语法 create trigger trigger_name {before(在执行操作之前触发)|after(在执行操作之后触发)|instead(更新操作 视图)} {event(insert,update,delete,truncate)} on table_name {for each row(对指定操作的每一行调用一次)|for each statement (只会为任何指定的操作执行一次，不管修改多少行)} execute procedu
OpenGauss触发器实验+触发器使用方法讲解（厦门大学）
RuanFun的博客
05-22 1294
OpenGauss上的触发器创建和数据库系统概论有较大的区别——需要先创建一个函数（返回的是触发器，然后在函数里面写条件），然后再创建一个触发器。先创建一个函数，返回的是触发器。
openGauss高级数据库管理:触发器
weixin_53596073的博客
04-28 403
启动触发器的事件，取值范围包括：INSERT、UPDATE、DELETE或TRUNCATE，也可以通过OR同时指定多个触发事件。约束触发器只能指定为FOR EACH ROW。用户定义的函数，必须声明为不带参数并返回类型为触发器，在触发器触发时执行。执行触发器时要提供给函数的可选的以逗号分隔的参数列表。触发器会在指定的数据库事件发生时自动执行函数。触发器函数是在触发事件发生前执行。触发器函数是在触发事件发生后执行。触发器函数直接替代触发事件。修改后的新触发器名称。触发器对应的表名称。
PostgreSQL中的事件触发器，用途多多
iihero@CSDN
03-21 1126
DBA的工作范围以内，比较头疼的几件事之一，恐怕就是数据被误删。于是经常看到网上的一些文字，诸如：”删库跑路“。那也只是戏说，真正追究起来，跑是不跑不掉的。所以，千万不要干傻事。经验老道的DBA，向来是拿”备份“技术作为生存第一要素。数据丢了，连过去的有效备份都不具备的话，那可真是要被迫跑路了。本文试着从另外一个角度，即防御角度，看看PostgreSQL中如何通过事件触发器来阻断用户的drop table或truncate table之类的操作。顺便对PostgreSQL中的事件触发器的使用做些介绍。
openGauss插件使用指南:SQL语法—CREATE TRIGGER（1）
weixin_53596073的博客
07-09 771
可选项，trigger_order特征中的{FOLLOWS|PRECEDES}控制触发器的优先触发顺序，B兼容性模式下允许对同一个表，在同一触发事件定义多个触发器，会按照触发器创建的先后顺序来决定触发的优先顺序（先创建的优先）。使用PRECEDES，最后一次使用的触发器与原始触发器最紧挨着，其他的触发器的优先级都顺序向前挤压。触发器名称，该名称在mysql兼容风格语法中可以指定模式，触发器模式必须与其所在表的模式相同，若不限定模式，触发器自动继承其所在表的模式，且同一个表的触发器不能重名。
PostgreSQL触发器详解
Focus on PostgreSQL
05-07 8830
1、触发器的定义 触发器声明了当执行一种特定类型的操作时数据库应该自动执行一个特殊的函数。触发器可以被附加到表(分区或不分区)、视图和外部表。 2、触发器的语法 CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } ON table_name [ FROM re...
PostgreSQL事件触发器
Focus on PostgreSQL
05-07 1955
和oracle类似，pg中触发器除了支持普通的dml触发器，还支持ddl语句，便是通过事件触发器实现的。 事件触发器，当数据库中发生某些DDL或DCL事件（ddl_command_start，ddl_command_end，sql_drop，table_rewrite）时，可以被捕获，并触发调用用户定义的事件触发器函数。 语法： CREATE EVENT TRIGGER name ON e...
pg触发器以及自己遇到的错误
Night_of_light的博客
12-28 1854
PostgreSQL 触发器 PostgreSQL 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。 下面是关于 PostgreSQL 触发器几个比较重要的点： PostgreSQL 触发器可以在下面几种情况下触发： 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。 在执行操作之后（在检查约束并插入、更新或删除完成之后）。 更新操作（在对一个视图进行插入、更新、删除时）。 触发器的 FOR EACH ROW 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 FOR
postgresql 触发器 简介（转）
行者常至
09-18 1269
以PostgreSQL 9.2为例, 介绍触发器的使用. 分两部分. 包含如下内容 : 一、 1. 什么是触发器? 触发器有什么用? 创建触发器的语法? 2. 什么是触发器函数, 触发器函数可以用哪些语言编写? 3. 触发器有哪些分类? 4. 同一个表或视图上可以建多少个触发器? 如果一个表或视图上有多个触发器, 调用顺序如何决定? 5. 同一个触发器函数可以被多个触发器调用吗? 触发器函数的返回
Postgresql 查看当前数据库所有的触发器
08-01 2505
SELECT * FROM pg_trigger 转载于:https://www.cnblogs.com/telwanggs/p/11281211.html
Postgresql查看自己创建的函数、过程、触发器函数
kmblack1的专栏
03-03 6819
1 首先获取用户oid 注意这里指的是每个数据对应一个用户,具体操作方法请参阅再谈PostgreSQL创建数据库. select * from pg_authid where rolname='core';
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DarkAthena

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值