本文基于多篇博文整理而来,目的是较全面的学会使用DB2触发器,后期再整理复杂的使用场景,看完本文应该能够自己创建一个基本的触发器。

1.什么是触发器
当一个指定的 SQL 操作(如 DELETE,INSERT,或者是 UPDATE 操作)作用于某张表时,
一个定义了一组操作的触发器就可以被激活。
触发器并不像参照完整性约束和检查约束那样,我们甚至可以使用对其他表来进行更新。

触发器是一种过程,与表关系密切,用于保护表中的数据。
当一个表被修改(insert、update或delete)时,触发器自动执行。
触发器可以实现多个表之间数据的一致性和完整性。

触发器分为:DML触发器、替代触发器、系统触发器。

DML触发器:可以在DML语句进行触发,可以在DML操作前或操作后进行触发,并且可以对每个行或语句操作上进行触发。
替代触发器:进行视图操作的一种处理方法。
系统触发器:可以在数据库系统的事件中进行触发,如系统的启动与关闭等。

2.DML触发器工作原理

触发器是利用了表数据发生变化时,自动生成的两个虚拟表,一个是inserted,一个是deleted,
这两个虚拟表从其他地方是检索不到的,只有触发器能读到。

当数据发生变化时,如果是新插入数据,则该数据会在inserted里面暂时保存。
而删除一条数据时,则会在deleted里面暂时保存。
更新则等于先删除再插入,也就是deleted里面存更新前,inserted里面存更新后。
这些数据会在触发器执行完成后消失。

3、DML触发器类型

1)BEFORE 触发器:在对表插入或更新之前执行该触发器,允许使用CALL 和 SIGNAL SQL 语句;

2)BEFORE DELETE 触发器:在删除操作之前执行该触发器;

3)AFTER 触发器:在更新、插入或删除操作之后执行。该触发器用于更新反映表间关系和一致性的其他表中的数据,还用于确保数据完整性。AFTER 触发器通常用于在特定情况下向用户生成报警;

4)INSTEAD OF 触发器:该触发器支持对不支持插入、更新和删除操作的视图执行这些操作;

4、基本语法

CREATE TRIGGER 1) trigger_name
2) [BEFORE/AFTER] 3)[INSERT/DELETE/UPDATE] ON 4) [table_name] 
5) [FOR EACH ROW]


属性解析:
1)触发器名称
2)触发器条件达成前还是达成后调用触发器
3)触发器被触发的条件(当table_name表执行insert/delete/update这三种操作哪一种时候被触发)
4)当table_name表被操作时候调用触发器
5)声明该触发器是一个行级触发器(如果未指明则被默认为语句级触发器)

触发器中使用NEW和OLD来获取某列的新旧属性
insert中只能用NEW,DELETE中只能用OLD,update中既可以用NEW,也可以用OLD

5. before举例--- before代表触发器里面的命令在DML修改数据之前执行

1)在emp表上面创建触发器,当输入的工资小于100时,自动将工资修改为100

create or replace trigger tri_emp_sal_check
before INSERT OR UPDATE ON emp
for each row
BEGIN
 IF :new.sal <100 THEN
 :new.sal :=100;
 END IF;
END;

执行DML语句

update emp set sal =90 where empno =3030;
commit;


发现工号3030员工的工资成功修改为100。

需要修改NEW值的时候就使用BEFORE,其余场景一律使用after

举个需要修改:NEW值的情况:某个字段数据库中是NUMBER型的,比如年龄,而送过来的数据是带字符的,比如“17岁”,
直接写入就报错了,此时用触发器把数据进行规范化处理后再写入数据库,就可以用before类型的数据库。

2)在sales表执行insert之前触发

CREATE TRIGGER sales_bi_trg #触发器声明
BEFORE INSERT ON sales #触发器被触发条件
FOR EACH ROW #声明为一个行级触发器
BEGIN
 DECLARE row_count INTEGER; #声明row_count变量
 SELECT COUNT(*) INTO row_count FROM customer_sales_totals WHERE customer_id=NEW.customer_id;
 IF row_count > 0 THEN
 UPDATE customer_sales_totals
 SET sale_value=sale_value+NEW.sale_value
 WHERE customer_id=NEW.customer_id;
 ELSE
 INSERT INTO customer_sales_totals
 (customer_id,sale_value)
 VALUES(NEW.customer_id,NEW.sale_value); 
 END IF;
END

语句分析:在sales表执行insert操作之前,查看customer_sales_totals表中是否有将要插入的客户记录,
有的话更新customer_sales_totals表,没有则在customer_sales_totals表中插入客户交易信息。

6.mysql触发器

在一个表上最多建立6个触发器,即
1)before insert型,
2)before update型,
3)before delete型,
4)after insert型,
5)after update型,
6)after delete型。

触发器的一个限制是不能同时在一个表上建立2个相同类型的触发器。
这个限制的一个来源是触发器程序体的“begin和end之间允许运行多个语句”(摘自mysql使用手册)

 

7.删除触发器

db2 drop trigger <trigger_name>

----------------------------------------------------------------------------------------------------------------------------------------------------------------

使用命令行方式:

su - db2inst1

db2 connect to 数据库名

db2 “触发器SQL语句”