Oracle触发器详解

开发中肯定会用到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