Klustron触发器介绍和案例测试
Klustron触发器介绍和案例测试
注意：
如无特别说明，文中的版本号可以使用任何已发布版本的版本号代替。所有已发布版本详见：Release notes
本文目标：
主要内容是首先介绍触发器的概述，介绍基于行级别（FOR EACH ROW）和语句级别（FOR EACH STATEMENT）上触发器，介绍创建触发器的基本语句和注意事项，然后测试基于FOR EACH ROW的触发器的测试案例和测试基于FOR EACH STATEMENT的触发器的测试案例。
01 触发器介绍
首先得提一下触发器函数，触发器函数与用户自定义函数类似，也是用户自定义的函数，不同的是触发器函数不需要在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触发器。
02 触发器创建语法
更具前面的介绍，我们可以已经触发器函数的与普通函数的区别：借助预定义的变量名来引用上下文中数据变化信息；特殊的返回值类型。除此之外，和其他的普通函数没什么太大区别。这里主要介绍的是触发器的创建，下面是创建触发器的语法规则：
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 要求创建触发器的表必须存在主键（后续版本可能去除这个限制）。
- 如果在分区表上创建语句级触发器，只有当用户显示更新分区表时会触发，单独更新分区表的子表不会触发；如果在分区表上创建行级触发器，当用户更新分区表或者单独更新分区表的子表时都会触发。
03 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的触发器的测试案例。
04 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的触发器的测试案例。