oracle触发器

一、五种触发器：语句触发器，行触发器，instead of触发器，系统事件触发器，用户事件触发器。

1.语句触发器

create tableemp_copy asselect empno,ename,job,sal from emp;

create tableemp_copy_log(who varchar2(30),when date);

 

create triggerbiufer_emp_copy

before insert orupdate or delete

on emp_copy

begin

  insert into emp_copy_logvalues(user,sysdate);

end;

 /

update emp_copy setsal=sal*2.0;

select *fromemp_copy_log;

2.行触发器

create tableperson(

personID numberprimary key,

personNamevarchar2(20));

create sequenceperson_seq;

 

create triggerbifer_person_personID_pk

before insert onperson

for each row

begin

  select person_seq.nextval

  into:new.personID from dual;

end;

/

 

insert intoperson(personName) values('Athos');

insert intoperson(personID,personName) values(5678,'Porthos');

insert intoperson(personName) values('Aramis');

select * fromperson;

3.instead of触发器

create or replaceview emp_job

as

selectempno,ename,job from emp;

 

create triggerupdate_emp_job_view1

 instead of update on emp_job

 begin

   update emp

   set empno=:new.empno,

       ename=:new.ename,

       job=:new.job

   where empno=:old.empno;

 end;

 

4.系统事件触发器：orcle系统允许开发人员建立与特定系统事件相关的触发器，特定事件如下：数据库启动，数据库关闭，服务器错误等。

create trigger ad_startup

after startup ondatabase

begin

  --pl/sql代码

end;

 

5.用户事件触发器

create tabledrop_objects(

object_name varchar2(30),

object_type varchar2(30),

dropped_on date

);

 

create or replace

 trigger lo_drop_trigger

 before drop on lzm.schema

 begin

   insert into drop_objects

  values(ora_dict_obj_name,ora_dict_obj_type,sysdate);

 end;

 /

create tabledrop_table(col1 number,col2 number);

create viewview_drop_table(columnA,columnB)

as

select col1,col2

from drop_table;

drop viewview_drop_table;

drop tabledrop_table;

select * fromdrop_objects;

 

二、禁用触发器：

altertrigger<trigger_name> disable;

altertrigger<trigger_name> enable;

三、查看触发器信息

selecttrigger_name,table_name from user_triggers;