(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;