(Oracle)触发器的相关知识与实例

触发器

触发器必须由事件触发.

触发事件分为3类:DML事件、DDL事件、数据库事件。

触发器分为4类：DML触发器、DDL触发器、数据库事件触发器、替代触发器。

注：触发器内不能使用commit，rollback，savepoint 语句，也不能直接或间接调用含有上述语句的存储过程和存储函数。

DML触发器

实例1

先创建一个触发事件记录表event_record

create table  event_record(

record_id   number(10) primary  key,

dml_name         varchar2(15),

table_name       varchar2(20),

time        date,

user_name        varchar2(20)

);

创建一个序列squ

create sequence  squ  increment by  1  start with  1  maxvalue 999999  nocycle  nocache;

创建并编译触发器dml_log

create or  replace  trigger dml_log

before                                   --在事件操作前触发

insert or  update  or delete           --触发事件

on emp                                 --触发器定义在emp表上

for each  row                           --行级触发器

begin

 if inserting  then

  insert  into  event_record values(squ.nextval,'INSERT','EMP',sysdate,user);

 elsif updating  then

  insert  into  event_record values(squ.nextval,'UPDATE','EMP',sysdate,user);

 else

  insert  into  event_record values(squ.nextval,'delete','EMP',sysdate,user);

 end  if;

end;

现在你对emp表进行DML操作来验证触发器的作用吧！

实例2

创建一个触发器change_sal，只能修改部门10的雇员工资

create or  replace  trigger change_sal

before

update of  sal

on emp

for each  row

begin

 if :new.deptno!=10  then                   --注意new和old，它们放在可执行代码中要加:，放在when条件中不用加:。

  raise_application_error(-20001,'您不能修改10号部门外的雇员工资！');    --用函数反馈错误信息

 end  if;

end;

实例3

创建级联删除触发器cascade_delete,当删除dept表中的部门时，级联删除emp表中雇员信息。

create or  replace  trigger cascade_delete

after             --在事件操作后触发

delete

on dept

for each  row

begin

 delete from  emp  where deptno=:old.deptno;

end;

实例4

创建一个语句级触发器oprate_trigger，限定只能emp表进行修改操作，不能对数据库进行插入和删除。

create or  replace  trigger oprate_trigger

before

update or  delete  or insert

on emp                         --注意下面一行没有for  each row ,所以是语句级触发器

begin

 if updating  then

   insert  into  event_record values(squ.nextval,'UPDATE','EMP',sysdate,user);

 else    

   raise_application_error(-20008,'您不能对emp表进行插入和删除操作！');

 end  if;

end;

DDL触发器

create or  replace  trigger nocreate_trigger

before

create

on schema                      --在当前模式

begin

 raise_application_error(-20006,'您在当前模式下，不能创建任何对象！');

end;

数据库事件触发器

先创建一个表user_log

create table  user_log(

user_name  varchar2(20),

logon_time  date,

logoff_time  date

);

创建并编译触发器datebase_logon

create or  replace  trigger datebase_logon

after

logon

on  schema  --当前模式

begin

 insert  into  user_log values(user,sysdate,'');

end;

创建并编译触发器datebase_logoff

create or  replace  trigger datebase_logoff

before

logoff

on  schema  --当前模式

begin

 insert  into  user_log values(user,'',sysdate);

end;

替代触发器

替代触发器只能创建在视图上，用来替代对视图进行的插入、修改和删除操作。

先创建一个视图

create view emp_ename  as  select ename  from  emp;

创建并编译触发器change_ename

create or  replace  trigger change_ename

instead of       --注意替代触发器的用法

insert

on emp_ename

declare

v_empno emp.empno%type;

begin     --注意里面的程序，当遇到对视图插入时，转化为对表的插入来达到对视图插入的目的(因为empno为表的主键)

 select max(empno)+1  into  v_empno from  emp;

 insert into  emp(empno,ename)  values(v_empno,:new.ename);

end;

其他知识(以触发器change_ename为例)

通过user_triggers视图来查看触发器：select * from user_triggers;

使触发器失效：alter  trigger change_ename  disable;

使触发器生效：alter  trigger change_ename  enable;

删除触发器：drop  trigger change_ename;