开发中肯定会用到oracle的触发器,本文进行详细讲解。
这里实例中用到的主要是oracle中scott用户下的emp以及dept表,数据如下
一、触发器概念
1、概念:
触发器的本质是一个存储过程,顾名思义发生特定事件时oracle会执行触发器中的代码。
细分它的组成可以分为3个部分:第一部分在什么条件下触发器会执行,即触发器被触发的事件。第二部分在什么时间点执行触发器
即触发器的发生事件例如before,after。第三部分触发器自身所要做的事情,就是触发器被触发以后具体想表达的事件,在begin和end
之间的sql。
二、触发器的分类:
1、ddl触发器:即执行ddl操作后所触发的事件。
常用的ddl操作有:grant(授权),revoke(撤销授权),create(创建),drop(删除),alter(修改),comment(注释),audit(审核),rename(重命名)
在进行具体实例以前先来讲解另一个概念:oracle中的user和schema:
user:oracle中的用户,拥有数据库的对象以及对数据库对象增删改查的权限。schema:该用户下所有数据库对象的集合collection.类似于生活中
房子schema和房子的拥有者user之间的关系,你是一个用户user你可以通过alter session查看别人的房子,但是你是否可以改变房子中的家具,要看这个房子的拥有者是否grant你这个权限,除非你是所有房子的最高权限人dba。
ddl example:禁止scott用户的所有ddl操作
create or replace trigger scott_trigger before ddl on schema begin raise_application_error(-20008,'禁止scott用户的所有ddl操作'); end;
create sequence myseq;
这里看到在创建触发器以后如果仍然使用ddl操作,便会报错。
2、dml触发器:基于dml操作的触发器,细分又可以分为行触发器和语句触发器。
a、语句触发器:dml操作可能会影响很多行,主要用于对数据的安全保护。
example:禁止在周四,周五修改emp表数据
create or replace trigger emp_trigger before update or delete or insert on emp begin if to_char(sysdate,'day') in ('星期四','星期五') then raise_application_error(-20008,'不允许在周四周五修改emp表'); end if; end;
update emp set sal=800;
这里建立触发器以后,当你想改变所有人的工资时就会出触发器的错误,所有人的工资即表示会影响很多行。
b、行级触发器:针对需要操作的那一行,有关键词:for each row,用来
(1)实现数据的审计功能:
example:做一个记录删除员工信息的表记录被删除员工的信息
这里为了不改变oracle中emp表的数据,新建一个emp_new表
create table emp_new as select * from emp;
create table emp_audit(name varchar2(10),delete_time date);
create or replace trigger delete_trigger after delete on emp_new for each row begin insert into emp_audit values(:old.ename,sysdate); end;
delete from emp_new where empno='7499';
select * from emp_audit;
这里可以看到在创建触发器时,用到了for each row关键词,:old.***用来表示更改以前的表中的数据,:new.***用来表示更改以后的数据,在删除数据以后在日志表就有对应的记录。
(2)实现数据完整性:
example:要求员工涨工资后,不能低于原来的工资,所涨工资也不能高于原来的50%。
这里为了不改变oracle中emp表的数据,新建一个emp_new表
create table emp_new as select * from emp;
create or replace trigger emp_trigger before update of sal on emp_new for each row when (new.sal<old.sal or new.sal>1.5*old.sal) begin raise_application_error(-20008,'工资只增不降,且涨幅不可大于50%'); end;
update emp_new set sal = 1.6*sal where empno='7788';
这里可以看到当改变数据时会触发触发器错误,对表中某一个字段的修改用update of即可,另外如果new和old在plsql块的外部
即begin外面不可以加冒号。
(3)参照完整性:
example:主要用于级联更新,如更新dept表中的deptno时,emp表的deptno也更新。
这里仍然新建2个表分别和emp表dept表的数据相同。
create table emp_new as select * from emp;
create table dept_new as select * from dept;
create or replace trigger cascade_trigger after update of deptno on dept_new for each row begin update emp_new set deptno=:new.deptno where deptno=:old.deptno; end;
update dept_new set deptno=15 where deptno=20;
select * from dept_new;
select * from emp_new;
这里参照完整新指具有主从关系的多个表,当更新主表主键时需要更新从表的相关数据。
3、替代触发器:
这里先讲另一个概念:带有with check option的视图:
如果视图的定义包括条件(如where子句)并且任何应用于该视图的insert或update语句都应包括该条件,则必须使用with check option定义该视图。
example:
create view emp_view (ename,empno) as select ename,empno from emp where deptno=20 with check option;
这里有个条件部门号为20,则任何修改这个视图的语句都必须针对的是20号部门的员工。
继续替代触发器的概念:关键字insteadof,主要针对一些复杂的视图,因为级联表所产生的视图不可以使用update,insert,delete等关键字,没有before,after等关键字,并且不可以建立在with check option选项的视图上,比如新建一个emp表和dept表的级联视图,则不可以向其中添加数据,现在通过触发器解决:
example:
仍然新建2个表分别和emp表dept表的数据相同。
create table emp_new as select * from emp; create table dept_new as select * from dept;
create view emp_dept as select d.deptno,d.dname,e.empno,e.ename from dept_new d,emp_new e where d.deptno=e.deptno;
这里scott用户需要先通过sysdba授权才能建立视图:
grant create view to scott;
create or replace trigger insteadof_trigger instead of insert on emp_dept for each row declare v_temp int; begin select count(*) into v_temp from dept_new where deptno=:new.deptno; if v_temp=0 then insert into dept_new(deptno,dname) values(:new.deptno,:new.dname); end if; select count(*) into v_temp from emp_new where empno=:new.empno; if v_temp=0 then insert into emp_new(deptno,empno,ename) values(:new.deptno,:new.empno,:new.ename); end if; end;
insert into emp_dept values(15,'humanresource',7999,'leaf');
select * from emp_new;
select * from dept_new;
这里触发器中当对视图进行insert时,会对相应的emp_new 和dept_new进行修改,也就做到了对复杂视图的修改。
4、系统触发器:
顾名思义,由系统触发器所触发的事件,常用的系统事件startup,shutdown,db_roll_change,server error等。
example:记录启动数据库时的事件以及时间。
此处因为是系统触发器,所以需要用sysdba的权限登陆。
create table event_table(event varchar2(50),event_time date);
create or replace trigger event_trigger after startup on database begin insert into event_table values(ora_sysevent,sysdate); end;
select * from event_table;
三、触发器的综合实例
example:做一个日志用来记录scott用户的一些操作:
首先在sysdba权限下建立日志表,序列,触发器:
create table object_log( logid number constraint pk_logid primary key, operatedate date not null, objecttype varchar2(50) not null, objectowner varchar2(50) not null );
create sequence obj_log_seq;
create or replace trigger object_trigger after create or drop or alter on database begin insert into object_log values(obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner); end;
在scott用户下随便创建个东西:
create sequence my_seq;
回到sysdba权限下查看日志表中是否有对应的记录:
select * from object_log;
发现有数据,说明一个日志表成功做好,监视一些用户操作的触发器就做好了。
至此,触发器全部说明完毕,不足之处还请评论说明,谢谢。 2018-08-27 16:50:28