人大金仓 KingbaseES PLSQL触发器基础理论

关键字：

DML、DDL

1.什么是触发器？

是一种数据库对象，可以看做由事件触发的特殊存储过程，当一个特定的事件发生后，会自动执行在数据库表上的某些操作，比如说对表进行（insert、delete、update）操作时激活触发器，使表中其他数据发生变化。

2.触发器的使用场景

• 数据验证：确保插入，更新或者删除操作符合业务规则和完整性约束条件。
• 数据转换：把插入、更新或者删除操作中的数据转换为其他格式或者单位。
• 数据记录：记录插入、更新或者删除操作的详细信息，例如时间戳、用户ID等。

3.触发器的种类

3.1DML触发器：

①在语法上，行级触发器和语句级触发器的区别是带不带FOR EACH ROW子句。在行级触发器中指定这个字句，语句级触发器不需要这个子句。

②行级触发器是一种基于行的触发器，他会在每次插入、更新或者删除单个记录时触发，即当DML语句对每一行数据进行操作时，都会引起该触发器的执行。语句级触发器是一种基于SQL语句的触发器，他会在SQL语句执行完毕后触发。即无论DML语句影响了多少行数据，其所引发的触发器仅执行一次。

总结：行级触发器会对数据库表中受影响的每一行触发一次触发器代码，而语句级触发器只触发一次，和语句所影响的结果的行数无关

3.1.1 DML触发器案例：

行级触发器：

test=# \set SQLTERM /
test=# create or replace trigger test_row_trigger before update on dmlt1
test-# for each row
test-# begin
test-# dbms_output.put_line('update666');
test-# end;
test-# /
CREATE TRIGGER
test=# \set SQLTERM ;

这里利用update语句连续更新两条数据，显示输出两次字符串。

语句级触发器：

test=# \set SQLTERM /
test=# create or replace trigger test_row_trigger before update on dmlt1
test-# begin
test-# dbms_output.put_line('update666');
test-# end;
test-# /
CREATE TRIGGER
test=# \set SQLTERM ;

这里连续更新两条数据，但是只输出一行字符串

3.2 DDL触发器（系统事件触发器）

1、对数据库实例或者是某个用户模式进行操作时定义的触发器

2、DDL触发器由DDL语句触发，例如create、drop等语句

3、DDL触发器也可以分为before和after类型触发器

3 替代（instead of）触发器

定义：

替代触发器和常见的after触发器不一样，after触发器是先建立临时表，然后实际更改数据，最后激活触发器。替代触发器可以在服务器执行触发instead of触发器的代码时，先建立临时表，然后直接触发instead of触发器，而拒绝执行用户的DML语句,即替代触发器会跳过DML语句并执行其他语句。

案例：

创建一张表t2，两列值分别为id1，id2，且都为int型此时替代触发器设定用户插入id1的值不可小于0，否则拒绝用户插入并直接修正用户的数据为0再插入

create table t2 (id1 int,id2 int);
test=# create or replace view t2_view as select * from t2;
CREATE VIEW
test=# \set SQLTERM /
test=# create or replace trigger t2_trigger instead of insert on t2_view
test-# for each row
test-# as
test-# begin
test-# if inserting and :new.id1<0 then
test-# insert into t2 values(0,:new.id2);
test-# else insert into t2 values(:new.id1,:new.id2);
test-# end if;
test-# end;
test-# /
CREATE TRIGGER
test=# \set SQLTERM ;

此时，对视图的编辑有如上语法，当id1列值小于0时，拒绝用户设定数据的插入，并执行instead of触发器预先设定的值。 案例如下图，当对id1列插入值为-1时，instead of触发器被触发，自动将数值修改为0。

注：Instead of触发器在SQLServer中可以直接作用于表，但是在kingbaseES中会报错，即不可作用于表，且通常作用于视图

4.创建触发器的语法

CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER}{INSERT | UPDATE | DELETE}[OF column_name][ON table_name]
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition)
DECLARE
--声明局部变量和游标
BEGIN
--执行触发器操作
END;
/

参数说明：

• CREATE OR REPLACE TRIGGER：创建或者替换一个触发器
• BEFORE|AFTER ：指定触发器的插入、更新或者删除操作之前还是之后触发
• INSERT | UPDATE | DELETE：指定触发器在哪种操作上触发
• OF column_name ：指定只对某一列操作
• ON table_name：指定触发器所属的表名
• FOR EACH ROW：指定为每一行执行触发器的操作
• WHEN (condition)：指定触发器执行的条件

更多信息，参见https://help.kingbase.com.cn/v8/index.html