Klustron触发器介绍和案例测试

KunlunBase 昆仑数据库 2023-08-26

231

点击上方蓝字关注我们

导读

注意：

如无特别说明，文中的版本号可以使用任何已发布版本的版本号代替。所有已发布版本详见：http://doc.klustron.com/zh/Release_notes.html。

本文目标：

主要内容是首先介绍触发器的概述，介绍基于行级别（FOR EACH ROW）和语句级别（FOR EACH STATEMENT）上触发器，介绍创建触发器的基本语句和注意事项，然后测试基于FOR EACH ROW的触发器的测试案例和测试基于FOR EACH STATEMENT的触发器的测试案例。

关键词：触发器、SQL、审计、AUDIT

触发器介绍

首先得提一下触发器函数，触发器函数与用户自定义函数类似，也是用户自定义的函数，不同的是触发器函数不需要在SQL中显示调用执行，而是由数据库系统在特定事件发生时自动去执行的（例如表/视图上的增删改等）。

因此，用户的在创建触发器函数这一特殊函数时，除了定义函数体之外，还需要指定该函数的执行时机，即指定哪些事件（增/删/该）发生在哪个对象（表/视图）之前/后时执行触发器函数，这样以来，一个完整的触发器就创建出来了。

所以触发器共包含两部分内容：1、触发器函数；2、触发器的定义（即定义触发器函数的执行时机）。

在Klustron中，触发器（Triggers）是用于在数据库中特定的操作（如插入、更新、删除）发生时自动执行的特殊函数。触发器可以在行级别（FOR EACH ROW）或语句级别（FOR EACH STATEMENT）上触发，这取决于触发器的定义。

以下是FOR EACH ROW和FOR EACH STATEMENT触发器的区别：

1. FOR EACH ROW 触发器：

FOR EACH ROW 触发器是在每行受影响时触发的。也就是说，当执行的操作影响到表中的每一行时，触发器会被执行。
FOR EACH ROW 触发器可以访问并操作正在被修改的行的数据，因为它是在每行之间触发的。
FOR EACH ROW 触发器通常用于需要对每个受影响的行执行特定操作的情况，例如在更新某一行时更新其他相关行。

2. FOR EACH STATEMENT触发器：

FOR EACH STATEMENT触发器是在执行语句完成后触发的，而不是在每行之间触发。
FOR EACH STATEMENT触发器不能直接访问或操作正在被修改的行的数据，因为它是在语句级别上触发的。
FOR EACH STATEMENT触发器通常用于需要对整个语句的结果执行操作的情况，例如在插入大批量数据时进行一些汇总计算或日志记录。

在编写触发器时，需要根据具体的需求和操作确定是使用FOR EACH ROW 还是FOR EACH STATEMENT触发器。

如果需要对每行进行个别操作或需要访问被修改的行数据，应选择FOR EACH ROW触发器；如果只需要对整个语句的结果进行操作，而不需要访问具体的行数据，应选择FOR EACH STATEMENT触发器。

触发器创建语法

更具前面的介绍，我们可以已经触发器函数的与普通函数的区别：借助预定义的变量名来引用上下文中数据变化信息；特殊的返回值类型。除此之外，和其他的普通函数没什么太大区别。这里主要介绍的是触发器的创建，下面是创建触发器的语法规则：

CREATE [ CONSTRAINT ] TRIGGER name -- 触发器名称
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] } -- 触发的时机(BEFORE/AFTER)和触发事件（例如增删改等）
ON table_name -- 触发器针对的表
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] -- 是否延迟执行
[ REFERENCING { { OLD | NEW } TABLE [ AS ] name } [ ... ] ] -- 见上文的'触发器参数'
[ FOR [ EACH ] { ROW | STATEMENT } ] -- 触发器的级别
[ WHEN ( condition ) ] -- 触发器时需要额外满足的条件
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) -- 触发器函数
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE

语法规则中的添加备注信息已经非常详细了，读者可以参考讲解触发器参数时给的例子来体会。这里再补充以下要点：

目前 Klustron 不支持延迟执行触发器（即提交事务时再执行触发器函数）
虽然创建触发器时可以为触发器函数指定的'参数'，但触发器函数仍然要求是一个无参数的函数；而这些这些传入的“参数”其实韩式存放在上面提到的上下文中，并且通过系统约定好的名称TG_ARGV来引用。
在更新操作并发量很高或者修改的数据量很大时，行触发器对性能的影响是非常明显的。它不仅阻止了DML语句的下推，也使得触发器函数被大量执行。如果我们只关心部分数据的修改，可以在创建触发器时指定WHEN子句，这样一来只当满足子句的条件时才会执行触发器函数，降低对性能的影响。
目前Klustron要求创建触发器的表必须存在主键（后续版本可能去除这个限制）。
如果在分区表上创建语句级触发器，只有当用户显示更新分区表时会触发，单独更新分区表的子表不会触发；如果在分区表上创建行级触发器，当用户更新分区表或者单独更新分区表的子表时都会触发。

FOR EACH ROW触发器案例

这个是基于FOR EACH ROW的触发器的测试案例，一张是业务表product表，一张是审计表product_record表，当对product表做的每一行操作，都会触发触发器对product表的每个操作行为记录到审计表product_record表中。

3.1 连接到数据库

[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres

3.2 创建业务表product和审计表product_record

CREATE TABLE "public"."product" (
  "pro_id" int8 primary key,
  "pro_name" varchar(100),
  "pro_type" varchar(100),
  "price" int8
);


CREATE TABLE "public"."product_record" (
  "pro_id" int8 ,
  "pro_name" varchar(100) ,
  "pro_type" varchar(100),
  "price" int8,
  "data_type" varchar(50),
  "update_time" varchar(50) 
);

3.3 创建触发器函数

create or replace function product_record_fun() 
returns trigger as $$
  BEGIN
    IF TG_OP = 'INSERT' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (new.pro_id, new.pro_name, new.pro_type, new.price, tg_op, to_char(now(),'YYYYMMDD'));
    ELSIF TG_OP = 'UPDATE' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (new.pro_id, new.pro_name, new.pro_type, new.price, tg_op, to_char(now(),'YYYYMMDD'));
    ELSIF TG_OP = 'DELETE' then
    INSERT INTO "public"."product_record"("pro_id", "pro_name", "pro_type", "price", "data_type", "update_time") 
    VALUES (old.pro_id, old.pro_name, old.pro_type, old.price, tg_op, to_char(now(),'YYYYMMDD'));
    END IF;
    return new;
  END;
$$
LANGUAGE plpgsql;

3.4 创建触发器

create trigger product_trigger after insert or delete or update on product for each row
execute function product_record_fun();

3.5 在业务表product插入数据

insert into product (pro_id,pro_name,pro_type,price) values (10001,'ipad','padnote',4500);
insert into product (pro_id,pro_name,pro_type,price) values (10002,'ipad8','padnote',6000);
insert into product (pro_id,pro_name,pro_type,price) values (10003,'ipone','phone',8000);
insert into product (pro_id,pro_name,pro_type,price) values (10004,'ipone14','phone',8800);
insert into product (pro_id,pro_name,pro_type,price) values (10005,'notebook','computer',10000);
insert into product (pro_id,pro_name,pro_type,price) values (10006,'notebook2','computer',12000);

3.6 查看product业务表，和审计表product_record

select * from product;
select * from product_record;

可以查看到product_record审计表中记录了新insert的6条记录。

3.7 对product业务表中的记录做update操作

update product set price=9000 where pro_id=10002;
update product set price=8500 where pro_id=10003;
update product set price=9800 where pro_id=10004;

3.8 查看update操作后，product业务表和product_record审计表中的记录

select * from product;
select * from product_record;

可以查看到product_record审计表中记录了新update的3条记录

3.9 对product业务表中的记录做delete操作

delete from product where pro_id=10001;
delete from product where pro_id=10005;

3.10 查看delete操作后，product业务表和product_record审计表中的记录

select * from product;
select * from product_record;

可以查看到product_record审计表中记录了新delete的2条记录。

以上是完成基于FOR EACH ROW的触发器的测试案例。

FOR EACH STATEMENT触发器案例

这个是基于FOR EACH STATEMENT的触发器的测试案例，一张是业务表emp表，一张是审计表product_record表，当对product表做的每一行操作，都会触发触发器对product表的每个操作行为记录到审计表product_record表中。

4.1 连接到数据库

[root@kunlun1 ~]# su - kunlun
[kunlun@kunlun1 ~]$ psql -h 192.168.56.112 -p 47001 postgres

4.2 创建业务表emp和审计表emp_audit

CREATE TABLE emp (
    empid   int primary key,
    empname varchar(50),
    salary int
);


CREATE TABLE emp_audit(
    operation  char(10),
    stamp timestamp,
    userid  varchar(50),
    empid   int,
    empname  varchar(50),
    salary int
);

4.3 创建触发器函数

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'DELETE', now(), user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'UPDATE', now(), user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'INSERT', now(), user, n.* FROM new_table n;
        END IF;
        RETURN NULL; 
    END;
$emp_audit$ LANGUAGE plpgsql;

4.4 创建触发器

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();


CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();


CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

4.5 在业务表emp插入数据

insert into emp (empid,empname,salary) values(1001,'test',5000);
insert into emp (empid,empname,salary) values(1002,'admin',8000);
insert into emp (empid,empname,salary) values(1003,'operator',10000);
insert into emp (empid,empname,salary) values(1004,'auditor',12000);
insert into emp (empid,empname,salary) values(1005,'viewer',9000);
insert into emp (empid,empname,salary) values(1006,'test2',6000);

4.6 查看emp业务表，和审计表emp_audit

select * from emp;
select * from emp_audit;

可以查看到emp_audit审计表中记录了新insert的6条记录。

4.7 对emp业务表中的记录做update操作

update emp set salary=9000 where empid=1002;
update emp set salary=11000 where empid=1003;
update emp set salary=12500 where empid=1004;

4.8 查看update操作后，emp业务表和emp_audit审计表中的记录

select * from emp;
select * from emp_audit;

可以查看到emp_audit审计表中记录了新update的3条记录。

4.9 对emp业务表中的记录做delete操作

delete from emp where empid=1001;
delete from emp where empid=1005;
delete from emp where empid=1006;

4.10 查看delete操作后，emp业务表和emp_audit审计表中的记录

select * from emp;
select * from emp_audit;

可以查看到emp_audit审计表中记录了新delete的3条记录。

以上是完成基于FOR EACH STATEMENT的触发器的测试案例。

文章扩展

无论是行级还是语句级触发器，都增加了查询处理的工作量，并且有可能因此在无意中严重影响查询语句的性能。

经典数据库时代，数据库系统无法水平扩容，这种影响就更加严重而且难解，只能换一台计算能力更强也昂贵得多的服务器。对于KunlunBase分布式数据库来说，用户可以按需增加计算节点来增加计算能力，因此触发器和存储过程带来的性能开销和计算负载压力可以通过增加计算节点来完美解决。

另外，触发器的常见用法有其他替代技术方案，可以参考： http://doc.klustron.com/zh/Use_advanced_SQL_capabilities_in_KunlunBase.html

END

产品文档

Klustron 快速入门：

https://doc.kunlunbase.com/zh/Klustron_Instruction_Manual.html

Klustron 快速体验指南：

https://doc.kunlunbase.com/zh/Klustron_Quickly_Guide.html

Klustron 功能体验范例：

https://doc.kunlunbase.com/zh/Klustron-function-experience-example.html

Klustron 产品使用和测评指南：

https://doc.kunlunbase.com/zh/product-usage-and-evaluation-guidelines.html

欢迎大家下载和安装KunlunBase数据库集群，并免费使用（无需注册码）。

KunlunBase 完整软件包下载：

http://downloads.kunlunbase.com

如需购买请邮箱联系sales_vip@kunlunbase.com，有相关问题欢迎添加下方小助手微信联系🌹

欢迎大家扫码👇添加小助手（备注：加入Klustron技术交流群）欢迎大家在交流群共同探讨更多问题及主题。

点击👆上方，关注获取源代码及技术信息~

文章转载自KunlunBase 昆仑数据库，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Klustron触发器介绍和案例测试

评论