trigger

trigger

 

一、类型：  

　　DML触发器：insert、update、delete  

　　替代触发器：针对视图  

　　系统触发器：针对系统事件

 

二、触发频率：  

　　语句级(statement):当触发事件发生时，该触发器只执行一次；  

　　行级(row):当触发事件发生时，针对会受到影响的每一行数据，触发器都会执行一次；

 

三、注意：  

　　一个表最多只能有12个触发器，且相互不可矛盾、重复；  

　　单个触发器最多为32KB；  

　　触发器中不能包含事物控制语句(commit,rollback,savepoint)；且其中调用的过程和函数语句中也不能有事物控制语言；  

　　当触发器中有 “for each row” 选项时，则表示为语句级触发器；         

　　　　缺省 “for each row” 选项时，before 和 after 触发器为语句级触发器，instead of 触发器为行触发器；  

　　系统事件：         

　　　　startup           启动数据库实例之后         

　　　　shutdown          关闭数据库实例之前         

　　　　servererror       出现错误之后         

　　　　logon             连接之后         

　　　　logoff            断开连接之前         

　　　　create                    

　　　　drop         

　　　　alter         

　　　　ddl         

　　　　grant         

　　　　revoke         

　　　　rename         

　　　　audit/noaudit                             

 

四、语法：

create or replace trigger 触发器名 出发时间 触发事件       

　　on 表名 [for each row]       

　　[when condition]       

　　[pl/sql]       

 

五、实例： 

5.1、简单 after update

create table test_dept as select * from test_dept;
create or replace trigger test_trigger01 
       after update  
       on test_dept for each row
begin
       dbms_output.put_line(:old.deptno||'=++++++='||:new.deptno);
end;

update test_dept set b='11111' where a='a';
commit;

drop trigger test_trigger01;
drop table test_dept;

 

5.2、deleting，updating，inserting应用，将某表中的数据修改记录插入到另一张表中

 --deleting   只有:old
 --inserting  只有:new
 --updating   :old和:new都有

create table test_dept as select * from dept;
create table test_dept_log as select a.*,'删除' as flag,sysdate as time_ from dept a where 1=2;

create or replace trigger test_trigger02 
       after update or delete or insert  
       on test_dept for each row
begin
       case 
         when updating then 
              insert into test_dept_log values(:old.deptno,:old.dname,:old.loc,'修改',sysdate);
         when inserting then
              insert into test_dept_log values(:new.deptno,:new.dname,:new.loc,'插入',sysdate);
         when deleting then 
              insert into test_dept_log values(:old.deptno,:old.dname,:old.loc,'删除',sysdate);
       end case;
       /*--也可以写成:
         if updating then 
              insert into test_dept_log values(:old.deptno,:old.dname,:old.loc,'修改',sysdate);
         elsif inserting then   --【注意是elsif，而不是elseif】
              insert into test_dept_log values(:new.deptno,:new.dname,:new.loc,'插入',sysdate);
         elsif deleting then 
              insert into test_dept_log values(:old.deptno,:old.dname,:old.loc,'删除',sysdate);
         end if;
       */
end;

update test_dept set loc='test' where deptno=20;       
insert into test_dept values(50,'wer','wer'); 
delete from test_dept where deptno=50;   
select * from test_dept_log;
select * from test_dept;  

drop table test_dept;       
drop table test_dept_log;
drop trigger test_trigger02;

5.3、当前用户不是scott，则不允许修改

create table test_dept as select * from dept;
create or replace trigger test_trigger03 
       before update or insert or delete 
       on test_dept
       for each row
--when ((select count(*) from dual where user)='SCOTT'>0)
when (upper(user)<>'SCOTT')
declare tt varchar2(20);
begin
  select user into tt from dual;
  raise_application_error(-20001,'当前用户是'||tt||',没有操作权限！');
end;

update test_dept set loc='test_' where deptno=20;
select * from test_dept;

drop table test_dept;
drop trigger test_trigger03;

5.4、instead of触发器，建立在视图上，且该视图没有指定 with check option 选项；

create table test01(
       a number(10),
       b number(10),
       c number(10));
create table test02 as 
       select * from test01 where 1=2;
create view test_view01 as 
       select a.a,b.b from test01 a,test02 b where a.a=b.a and a.b=b.b;

create or replace trigger test_trigger04 
       instead of insert
       on test_view01
       for each row
begin
       --raise_application_error(-20001,'视图不可插入数据！');
       insert into test01 values (:new.a,null,null);
       insert into test02 values (null,:new.b,null);
end;

insert into test_view01 values(1,1);
insert into test_view01 
       select 2,2 from dual
       union all
       select 3,3 from dual;
select * from test01;
select * from test02;

drop view test_view01;
drop trigger test_trigger04;
drop table test02;
drop table test01;

5.5、将用户登陆和登出记录到数据表
--创建系统触发器，用户需要有administer database trigger权限
--以下操作在sys用户下可成功完成

create table test_login(
       event_ varchar2(10),
       user_ varchar2(20),
       address_ varchar2(100),
       date_ date
       );
create or replace trigger test_trigger05 
       after logon on database
begin
       insert into test_login values('登陆',user,ora_client_ip_address,sysdate);
end;        

create or replace trigger test_trigger06 
       before logoff on database
begin
       insert into test_login values('登出',user,ora_client_ip_address,sysdate);
end; 

drop table test_login;
drop trigger test_trigger05;
drop trigger test_trigger06;