1. 什么是DML触发器?
触发器是一个类似于存储过程的PL/SQL单元,可以存储在数据库中,并能重复调用,与存储过程不同的是,触发器只能被启用或禁用,但不能进行显示调用。只要发生指定事件,数据库就会调用它。
根据创建方式,触发器被分为两类。如果触发器是在模式或数据库上创建的,那么触发事件由DDL或数据库操作组成,触发器称为系统触发器;如果触发器是在表或视图上创建的,则触发事件由DML语句组成,触发器称为DML触发器。
2. DML触发器的组成及使用场景
DML触发器的组成:
触发事件:引发触发器被触发的事件,DML语句(INSERT、UPDATE、 DELETE语句对表和视图执行数据处理操作)。
触发时间:即该触发器是在触发事件发生前(BEFORE)还是之后(AFTER)触发。
触发操作:触发器触发后要完成的事情。
触发对象:包括表、视图。
触发条件:有WHEN子句指定一个逻辑表达式,只有当该表达式为TRUE时,遇到触发事件才会自动执行触发操作。
触发频率:说明触发器内定义的动作被执行的次数。即语句级触发器和行级触发器。
DML触发器的使用场景:
启用/禁用对表和视图的修改。
强制数据一致性。
提供审计和日志记录。
防止无效的事务处理。
启用复杂的业务逻辑。
自动生成虚拟列值
3. 行级触发器和语句级触发器的区别
语法的不同:
在语法上,行级触发器和语句级触发器的区别是带不带FOR EACH ROW子句。在行级触发器中指定这个字句,语句级触发器不需要这个子句。
在行级触发器中,当触发事件是INSERT和UPDATE时,可以引用:NEW.COLUMN_NAME字段来表示新插入或修改的行数据,当触发事件是DELETE和UPDATE时,可以引用:OLD.COLUMN_NAME字段来表示被删除或修改的行数据。
而在语句级触发器中,引用:NEW和:OLD是不正确的。
执行次数的不同:
行级触发器对DML语句影响的每个行执行一次,语句级触发器对每个DML语句执行一次。如果一条INSERT语句在TABLE表中插入500行,那么这个表上的语句级触发器只执行一次,而行级触发器就需要执行500次了。
4. INSTEAD OF DML触发器
INSTEAD OF DML触发器也可以称为视图触发器,是在非编辑视图或非编辑视图的嵌套列表上创建的DML触发器。 INSTEAD OF触发器是行级触发器,不能指定事件的触发事件,可以对OLD和NEW值进行读取,但不能更改他们。它的主要作用是实现对视图进行增删改时,同时对视图相关的表进行增删改。
INSTEADOF触发器语法树:
- CREATE OR REPLACE TRIGGER trigger_name
INSTEADOF触发器实例;
- --创建视图触发器,实现向视图中插入数据,可以同时向构成视图的多个表插入数据。
- --1.创建两个测试表t1和t2
- create table if not exists t1(id int primary key,name varchar(20));
- create table if not exists t2(t_num int primary key,value varchar(20));
- --2.使用触发器和序列给两个表格实现主键自增功能
- --2.1创建序列
- create sequence seq_t1;
- create sequence seq_t2;
- --2.2创建触发器
- --2.2.1给t1创建主键自增触发器
- \set SQLTERM /
- CREATE OR REPLACE TRIGGER tri_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
- BEGIN
- SELECT seq_t1.nextval INTO :new.id FROM DUAL;
- END;
- /
- --2.2.1给t2创建主键自增触发器
- CREATE OR REPLACE TRIGGER tri_insert_t2 BEFORE INSERT ON t2 FOR EACH ROW
- BEGIN
- SELECT seq_t2.nextval INTO :new.t_num FROM DUAL;
- END;
- /
- \set SQLTERM ;
- --测试主键自增功能
- delete from t1;
- delete from t2;
- insert into t1(name) values('xuyihang');
- insert into t2(value) values('100g');
- --结果查询
- --3.创建两个表t1,t2的查询结果视图
- create or replace view v_t1_t2 as select t1.id,t1.name,t2.t_num,t2.value from t1,t2;
- --查询视图中的数据
- --4.测试未使用视图触发器前,能否实现向v_t1_t2中插入数据,同时向表t1和t2中插入数据
- insert into v_t1_t2(id,name,t_num,value) values(2,'fmm',2,'200g');
- --5.创建视图触发器
- \set SQLTERM /
- CREATE OR REPLACE TRIGGER tri_insert_t1_t2 instead of INSERT ON v_t1_t2 FOR EACH ROW
- BEGIN
- insert into t1(id,name) values (:NEW.id,:NEW.name);
- insert into t2(t_num,value) values (:NEW.t_num,:NEW.value);
- END;
- /
- \set SQLTERM ;
- --创建视图触发器后测试
- insert into v_t1_t2(id,name,t_num,value) values(2,'fmm',2,'200g');
- --测试结果
5. 总结
本文介绍数据库对象触发器中的DML触发器,介绍了DML触发器的定义,DML触发器的组成及使用场景、行级触发器和语句级触发器的区别及INSTEAD OF触发器。并对行级触发器和INSTEAD OF触发器的使用进行了举例。