PL/SQL 觸發器

觸發器的一個明顯的特性就是不能被顯式地調用，當觸發事件發生時就會隐式地執行該觸發器，而且觸發器是不接收參數的。

觸發器本身就是一個命名的語句塊。

1.完成表的變更校驗

2.自動資料庫維護

3.控制資料庫管理活動

觸發器組成部分：

1.觸發器觸發的事件

2.觸發事件所在的對象

3.觸發器觸發的條件

4.觸發器被觸發時所要執行的語句塊，或稱觸發器體，是一個包括SQL語句和PL/SQL語句的過程調用或PLSQL塊，或者是被封裝在PLSQL塊中的java程式.

觸發器定義示例

create or replace tregger t_verifysalary

before update on emp for each row when(new.sal>old.sal)

declare

v_sal number;

begin

if updating('sal') then

v_sal:=:NEW.sal - :OLD.sal;

delete from emp_history where empno=:OLD.empno;

insert into emp_history values (:OLD.empno，:OLD.ename，:OLD.job,:OLD.mgr，:OLD.hiredate，:OLD.sal，:OLD.comm，:OLD.deptno);

update emp_history set sal=v_sal where empno=:NEW.empno;

end if;

end;

觸發器的分類

1.行觸發器與語句觸發器：行觸發器會對資料庫表中的每一行觸發一次觸發器代碼，語句觸發器則僅觸發一次，與語句所影響的行數無關。

2.before觸發器與alfter觸發器：是指與觸發時機相關的觸發器。

3.instead of觸發器：又稱為替代觸發器，是指不直接執行觸發語句，一般用在視圖更新的場合。

4.系統事件觸發器與使用者事件觸發器：在發生系統級的事件時。比如資料庫啟動，伺服器錯誤消息時間觸發時。

5.DML觸發器

6.系統觸發器：對資料庫執行個體或某個使用者模式進行操作時的觸發器，是以可以定義資料庫系統觸發器和使用者觸發器

7.替代觸發器：當對視圖進行操作時定義的觸發器。

DML觸發器

1.單行觸發器執行順序

當在某一行上定義了多個觸發器時

1.before語句觸發器

2.before行級觸發器

3.執行DML

4.after行級觸發器

5.after語句觸發器

2.多行觸發器執行順序

如果觸發器影響到多行，那麼在每一行上都要執行一次觸發器語句，假定觸發器影響到兩行，則其執行順序

2.第一行的before行觸發器

3.第一行執行DML

4.第一行after行級觸發器

5.第二行before行級觸發器

6.第二行執行DML

7.第二行alfter行級觸發器

8.after語句觸發器

在使用update作為觸發行為時，還可以使用update of 來指定一個或多個字段

before update of empno，ename，sal on emp

示例記錄日志觸發器

建立一個日志記錄表，當使用者對emp表進行新增，修改或删除時，會将修改記錄記錄到這個日志表中，以便知道對emp表的更改曆史記錄。

create table emp_log(

log_id number，

log_action varchar2(100)，

log_date DATE,

empno number(4),

ename varchar2(10)，

job varchar2(18),

mgr number(4),

hiredate date，

sal number(7,2)，

comm number(7,2),

deptno number(2)

);

create or replace trigger t_emp_log

after insert or delete or update on emp for each row

if inserting then

insert into emp_log values(emp_seq.nextval，'INSERT',sysdate，:new.empno，:new.ename，:new.job，:new.mgr，:new.hiredate，:new.sal，:new.comm，:new.deptno);

insert into emp_log values(emp_seq.nextval，'UPDATE_NEW',sysdate，:new.empno，:new.ename，:new.job，:new.mgr，:new.hiredate，:new.sal，:new.comm，:new.deptno);

insert into emp_log values(emp_seq.currval，'UPDATE_OLD'，sysdate，:old.empno，:old.ename，:old.job，:old.mgr,:old.hiredate，:old.sal，:old.comm，:old.deptno);

elsif deleting then

insert into emp_log values(emp_seq.nextval，'DELETE'，:old.empno，:old.ename，:old.job，:old.mgr,:old.hiredate，:old.sal，:old.comm，:old.deptno);

使用語句觸發器

如果在建立觸發器時，不指定for each row子句，那麼建立的觸發器就是語句觸發器，否則為行觸發器。語句觸發器每次觸發器觸發時僅執行一次.

1.使用before語句觸發器

使用語句觸發器限制修改

對emp表的更改隻能在正常工作日的8：30~18：00之内，不再這個時間的修改都不能進行

create or replace trigger t_verify_emptime

before insert or delete or update on emp

if(TO_CHAR(sysdate，'DAY') IN （'星期六'，'星期天'）) or (TO_CHAR(sysdate，'HH24:MI') NOT BETWEEN '08:30' ADN '18:00')

then

raise_application_error(-20001，'不能在非常時間段内操縱emp表');

2.使用after語句觸發器

after語句觸發器在所有的觸發器都執行完成之後，最後被觸發，在這個階段可以進行一些審計工作，比如統計自觸發器添加到現在以來所執行過的dml語句的次數和最後執行的時間，以便于根據這個結果進行性能的分析。

create table audit_table(

table_name varchar2(20)，

ins_count int,

udp_count int,

del_count int,

start_time date，

end_time date

create or replace trigger t_audit_emp

after insert or update or delete on emp

v_temp int;

select count(*) into v_temp from audit_table where table_name='EMP';

if v_temp=0

insert into audit_table values('EMP',0,0,0,SYSDATE,NULL);

case

when inserting then

update audit_table set ins_count=ins_count+1，end_time=sysdate where table_name='EMP';

when updating then

update audit_table set udp_count=udp_count+1，end_time=sysdate where table_name='EMP';

when deleting  then

update audit_table set del_count=del_count+1，end_time=sysdate where table_name='EMP';

end case;

使用OLD和NEW謂詞

1.當在insert語句上激發觸發器時，OLD結構是不包含任何值的

2.當在update語句上激發觸發器時，OLD和NEW結構都是具有值，OLD包含在更新之前記錄的值，NEW包含了在更新之後記錄的值。

3.當在DELETE語句上激發觸發器時，NEW結構不包含任何值，OLD結構包含已經被删除的記錄

4.NEW和OLD謂詞也包含了rowid僞列，這個僞列在OLD和NEW結構中具有相同的值。

5.不能更改OLD結構的值，如果這樣做會觸發ORA-04085錯誤。但是可以修改NEW結構的值

6.在觸發器内部，不能将NEW或OLD結構作為一個記錄參數傳遞給過程或函數，僅能傳遞單個的字段

7.當在匿名塊或觸發器内部使用NEW和OLD謂詞時，必須要在前面加上冒号。

8.在NEW和OLD結構中不能進行記錄級别的操作，比如直接為記錄指派是非法的。

示例

create table emp_data

(

emp_id int，

empno number,

ename varchar2(20)

create table emp_data_his

emp_id int,

create or replace trigger t_emp_data

before insert on emp_data for each row

emp_rec emp_data%ROWTYPE;

select emp_seq.nextval into :NEW.emp_id from dual;

emp_rec.emp_id:=:NEW.emp_id;

emp_rec.empno:=:NEW.empno;

emp_rec.ename:=:NEW.ename;

insert into emp_data_his values emp_rec;

使用referencing子句

在觸發器中也可以使用referencing子句來更改預設的謂詞名稱，比如可以将new子句更改為emp_new，将old子句更改為emp_old這樣的别名。

在指定了别名後，就可以在觸發體中使用:old_name和:new_name來代替:OLD和:NEW謂詞。

create or replace trigger t_vsal_ref

before update on emp

referencing OLD as emp_old NEW as emp_new for each row when(emp_new.sal>emp_old.sal)

if updating ('sal') then

v_sal:=:emp_new.sal - :emp_old.sal;

delete from emp_history where empno=:emp_old.sal;

insert into emp_history values(:emp_old.empno,:emp_old.ename,:emp_old.job,:emp_old.mgr,:emp_old.hiredate,:emp_old.sal,:emp_old.comm,:emp_old.deptno);

update emp_history set sal=v_sal where empno=:emp_new.empno;

使用when子句

when子句是在觸發器被觸發後，用來控制是否執行觸發體代碼的一個控制條件，在when子句中可以使用不帶冒号的new和old謂詞通路記錄的值，可以使用複合條件表達式組織多條記錄。

create or replace trigger t_emp_comm

before update on emp for each row when(new.comm>old.comm)

if updating ('comm') then

v_comm:=:NEW.comm - :OLD.comm;

insert into emp_history values(:OLD.empno，:OLD.ename,:OLD.mgr，:OLD.hiredate，:OLD.sal，:OLD.comm，:OLD.deptno);

update emp_history set comm=v_comm where empno=:NEW.empno;

使用條件謂語

條件謂語主要用來确定觸發器的DML語句的類型。

1.INSERTING

2.UPDATING

3.DELETING

使用UPDATING謂詞判斷特定字段的更新

create or replace trigger t_comm_sal

before update on emp for each row

when updating('comm') then

if :NEW.comm<:OLD.comm then

raise_application_error(-20001，'新的comm值不能小于舊的comm值');

when updating('sal') then

if :NEW.sal<:OLD.sal then

raise_application_error(-20001，'新的sal值不能小于舊的sal值');

控制觸發順序

使用FOLLOWS子句

create table trigger_data

（

trigger_id int，

trigger_name varchar2(100)

）;

create or replace trigger one_trigger

before insert  on trigger_data for each row

:NEW.trigger_id:=:NEW.trigger_id+1;

dbms_output.put_line('觸發了one_trigger');

create or replace trigger two_trigger

before insert on trigger_data for each row follows one_trigger  --讓該觸發器在one_trigger後面觸發

dbms_output.put_line('觸發了two_trigger');

if :NEW.trigger_id > 1

:NEW.trigger_id:=:NEW.trigger_id+2;

實際上應用follows子句後，在兩個觸發器之間建立了依賴。使得two_trigger依賴于one_trigger，可以通過以下sql語句查詢

sql>select referenced_name，referenced_type，dependency_type from user_dependencies where name='TWO_TRIGGER' and referenced_type='TRIGGER';

觸發器限制

在編寫觸發器時，需要注意不能對觸發其所應用的基表中讀取或修改資料。

1.通常 行級别的觸發器不能讀寫觸發器所作用的基表，這個限制僅應用在行級觸發器上，但是語句級的觸發器可以自由地讀寫觸發器基表

2.如果在觸發器中使用自治事務，并在觸發體中送出事務，則可以查詢基表的内容，但是不能對基表進行任何的修改操作。

使用自治事務

1.如果在觸發器中抛出一個異常，将導緻整個事務復原

2.如果在觸發體中使用了DML操作，比如像日志表中插入日志記錄，那麼這些DML操作也屬于主事務的一部分，是以觸發體中任何意外操作也會導緻整個事務復原

3.在觸發體中不能使用commit或rollback語句，因為這會影響到主事務的執行

before update on emp for each row when(NEW.comm>OLD.comm)

v_comm number;

pragma autonomous_transaction;

if updating （'comm'） then

v_comm := :NEW.comm - :OLD.comm;

delete from emp_history where empno=:OLD.comm;

insert into emp_history values(:OLD.empno，:OLD.ename,:OLD.job，:OLD.mgr，:OLD.hiredate，:OLD.sal，:OLD.comm，:OLD.deptno);

commit;

exception 

when others then

rollback;

替代觸發器

替代觸發器是觸發器類型中的另外一種，這種觸發器隻能定義在視圖上，當要對一個不能進行修改的視圖進行資料修改的時候，或者要修改視圖中的某個嵌套表的列時，可以使用替代觸發器。

替代觸發器，又稱為instead of 觸發器，之是以取這個名字，是因為觸發器将替代原來的資料操作語句的執行，更改為使用在觸發器中定義的語句來執行資料操作。

在學習視圖時曾經了解到，一些簡單的單表視圖，可以直接對其應，insert，update或delete語句進行更新，但是對于一些複雜的視圖，比如當視圖符合以下任何一種時，不能進行DML操作：

1.在定義視圖的查詢語句中使用了集合操作符，比如UNION,UNION ALL,INTERSECT,MINUS等

2.在視圖中使用了分組函數，比如MIN,MAX,SUM,AVG,COUNT等

3.使用了GROUP BY,CONNECT BY或START WITH等子句

4.具有DISTINCT關鍵字

5.使用了多表連接配接查詢

如果要對這種視圖進行修改，可以通過在視圖上編寫一個替代觸發器來完成正确的工作，這樣就允許對它進行修改了。

當使用者進行DML進行視圖操作時候，通過替代觸發器，将這些DML語句對視圖的更改替換成對基表的DML操作。注意事項：

1.替代觸發器隻能用于視圖

2.當建立替代觸發器時，不能指定before和after選項

3.當對視圖建立替代觸發器時，必須指定for each row方法

建立視圖

create or replace view scott.emp_dept(empno，ename，job，mgr，hiredate，sal，comm，deptno，dname，loc)

as

select emp.empno，emp.ename，emp.job，emp.mgr，emp.hiredate，emp.sal，emp.comm，emp.deptno，dept.dname，dept.loc from dept，emp where ((dept.deptno=emp.deptno));

create or replace trigger t_dept_emp

instead of insert on emp_dept

referencing new as n for each row

v_counter int;

select count(*) into v_counter from dept where deptno=:n.deptno;

if v_counter=0

insert into dept values(:n.deptno，:n.dname，:n.loc);

select count(*) into v_counter from emp where empno=:n.empno;

insert into emp(empno，ename，job，mgr，hiredate，sal，comm，deptno) values （:n.empno，:n.ename，:n.job，:n.mgr，:n.hiredate，:n.sal，:n.comm，:n.deptno）;

替代觸發器必須使用for each row，表明對視圖的操作是一個行級的觸發器。

UPDATE與DELETE替代觸發器

create or replace trigger t_dept_emp_update

instead of update on emp_dept referencing NEW as n OLD as o

for each row

select count(*) into v_counter from dept where deptno=:o.deptno;

if v_counter>0

update dept set dname=:n.dname，loc=:n.loc where deptno=:o.deptno;

update emp set ename=:n.ename，job=:n.job，mgr=:n.mgr，hiredate=:n.hiredate，sal=:n.sal，comm=:n.comm,deptno=:n.deptno where empno=:o.empno;

DELETE觸發器

create or replace trigger t_dept_emp_delete

instead of update on emp_dept

referencing OLD AS o

delete from emp where empno=:o.empno;

delete from dept where deptno=:o.deptno;

替代觸發器完整示例

create or replace trigger t_emp_dept

instead of update or insert or delete on emp_dept

referencing NEW as n OLD as o

update dept set dname:=n.dname，loc=:n.loc where deptno=:o.deptno;

when deleting then

delete from dept where deptno=:o.deptno; 

insert into emp(empno，ename，job，mgr，hiredate，sal，comm，deptno) values （:n.empno，:n.ename，:n.job，:n.mgr，:n.hiredate，:n.sal，:n.comm,:n.deptno）;

delete from emp empno=:o.empno;

嵌套表替代觸發器

如果在視圖的表列中使用了嵌套表，在要對視圖進行更新時，必須使用替代觸發器

僅在定義的視圖中包含的嵌套表列中才能使用替代觸發器，隻有使用THE()或TABLE()子句來修改視圖所包括的嵌套表上的列時，觸發器才會觸發。當視圖上的DML語句被執行時，觸發器不會被觸發.

建立用于嵌套表的對象類型

create or replace type emp_obj as object(

job   varchar2(10)，

mgr   number(4),

hiredate DATE,

sal   number(7,2),

comm  number(7,2)，

嵌套表類型

create or replace type emp_tab_type as table of emp_obj;

嵌套表視圖，MULTISET必須與cast一起使用

create or replace view dept_emp_view as 

select deptno,dname,loc,cast(MULTISET(select * from emp where deptno=dept.deptno) as emp_tab_type) emplst from dept;

不能直接在嵌套表視圖執行DML

建立嵌套表替代觸發器

create or replace dept_emp_innerview

instead of insert

on nested table emplst of dept_emp_view

insert into emp(deptno,empno，ename，job，mgr，hiredate，sal，comm) values（:PARENT.deptno，:NEW.empno，:NEW.ename，:NEW.job，:NEW.mgr，:NEW.hiredate，:NEW.sal，:NEW.comm）;

代碼中使用了PARENT謂詞擷取嵌套表父行的deptno部門編号。

insert into table(select emplst from dept_emp_view where deptno=10) values (8003,'四爺','皇上'，NULL,SYSDATE,5000,500,10);

嵌套表替代觸發器與普通的替代觸發器的建立方式基本相同，但是有如下兩個基本的差別

1.嵌套表使用"on nested table" 嵌套表列 of 嵌套表視圖 這種定義方式

2.parent 謂詞在嵌套表替代觸發器中具有值，指向包含嵌套表的視圖的父項記錄。

系統時間觸發器

DML觸發器和替代觸發器都是在DML事件上觸發的，相反，系統觸發器是在DDL事件和資料庫伺服器事件時觸發的，DDL包含create，alert或drop等語句，使得資料庫管理人員可以監控對資料庫的更改。

示例：如果要知道在scott下，建立表時的各類資訊，可以通過一個DDL觸發器，通過監控對CREATE語句的應用來實作。

在scott使用者下建立一個儲存DDL建立資訊的表

create table created_log

obj_owner varchar2(30)，

obj_name  varchar2(30),

obj_type varchar2(20)，

obj_user varchar2(30)，

created_date DATE

create or replace trigger t_created_log

after create on scott.schema

insert into scott.created_log(obj_owner,obj_name，obj_type，obj_user，create_date) values（sys.dictionary_obj_owner，sys.dictionary_obj_name，sys.dictionary_obj_type，sys.login_user,SYSDATE）;

觸發器可以在DATABASE或SCHEMA級别進行定義，這兩個關鍵詞用來确定系統觸發器的級别。

startup 和 shutdown觸發器隻能在database級别上建立，在方案級别上建立沒有意義，是以不會被觸發。

建立兩個觸發器，都用來監控使用者的LOGON事件，一個在方案級别觸發，一個在資料庫級别觸發。

create table log_db_table

username varchar2(20)，

logon_time DATE，

logoff_time DATE,

address varchar2(20)

create table log_user_table

username varchar2(20),

logon_time DATE,

以DBA登入建立DATABASE級别的LOGON事件觸發器

create or replace trigger t_db_logon

after logon on database

insert into log_db_table(username,logon_time，address) values(ora_login_user，SYSDATE,ora_client_ip_address);

以scott登入，建立SCHEMA級别的事件觸發器

create or replace trigger t_user_logon

after logon on schema

insert into log_user_table(username，logon_time，address) values(ora_login_user，SYSDATE,ora_client_ip_address);

database級的記錄所有與資料庫連接配接相關的記錄。

觸發器屬性清單

oracle在DBMS_STANDARD包中提供了一些功能性的函數，以便在開發系統級别的觸發器時可以提供一些系統級别的資訊。

示例：startup和shutdown觸發器

create table event_table（

sys_event varchar2(30),

event_time DATE

create or replace trigger t_startup

after startup on database

insert into event_table values(ora_sysevent，SYSDATE);

before shutdown on database

屬性函數使用示例

在屬性函數清單中，ora_is_drop_column和ora_is_alter_column是兩個非常有用的屬性函數，在很多場合，可能希望一些表的字段不能被修改或移除，這樣在多人開發時可以防止開發人員的意外操作而出現意外，此時可以考慮建立alter或drop系統觸發器時，使用這兩個屬性函數來避免使用者進行非法的删除。

create or replace trigger preserve_app_cols

after alter on schema

cursor curs_get_columns(cp_owner varchar2，cp_table varchar2)

is

select column_name from all_tab_columns where owner=cp_owner and table_name=cp_table;

if ora_dict_obj_type ='TABLE'

for v_column_rec in curs_get_columns(

ora_dict_obj_owner，

ora_dict_obj_name

loop

if ora_is_alter_column(v_column_rec，column_name)

if v_column_rec.column_name='EMPNO' then

raise_application_error(-20003,'不能對empno字段進行修改');

end loop;

定義SERVERERROR觸發器

servererror事件可以用來跟蹤資料庫中發生的錯誤，錯誤代碼可以通過server_error屬性函數在觸發器内部得到，可以通過該函數确定堆棧中的錯誤代碼，可以使用DBMS_UTILITY.FORMAT_ERROR_STACK擷取錯誤資訊。

使用after servererror時必須要了解如下的錯誤是否會被觸發

ORA-00600：oracle内部錯誤

ORA-01034：oracle不可用

ORA-01403: 沒有找到資料

ORA-01422:提取操作傳回大于請求的行數

ORA-01423:在一個提取操作中檢測到額外的行

ORA-04030:在配置設定位元組時記憶體不夠。

after servererror觸發器在觸發器内部産生異常時也不會觸發，這樣會導緻死循環。

使用after servererror觸發器記錄錯誤日志

create table servererror_log(

error_time date，

username varchar2(30)，

instance number，

db_name varchar2(50)，

error_stack varchar2(2000)

create or replace trigger t_logerrors

after servererror on database

insert into servererror_log values(sysdate，login_user，instance_num，database_name，DBMS_UTILITY.format_error_stack);

觸發器的事務與限制

1.startup和shutdown觸發器不可以有任何條件，不能使用when子句

2.servererror觸發器可以用errno測試來檢查具體的錯誤

3.logon和logoff觸發器可以用userid或username測試來檢查使用者的辨別符或使用者名

4.DDL觸發器可以使用when子句檢查正被修改的對象的類型和名稱，并且可以檢查使用者辨別符或使用者名。

觸發器的管理

查詢emp表上定義了哪些觸發器

select trigger_name，trigger_type，table_name，triggering_event， status from user_triggers

where table_name='EMP';

如果想要建立一個一開就是被禁用的觸發器

create or replace trigger t_temp_testing

after insert on emp

disable

NULL;

觸發器名稱與權限的管理

觸發器的命名具有自己的名稱空間，所謂的名稱空間是指在這個範圍内用于對象名稱的合法辨別符集，所有這個名稱空間内的對象的命名必須唯一。觸發器的名稱空間與子程式，包和表的名稱空間不同。子程式，包和表具有相同的名稱空間，是以在一個方案内，如果子程式，包或表任何一個具有相同名稱，都會導緻不合法的命名。而觸發器存在于單獨的名稱空間，是以可以與表和過程具有相同的名稱，都會導緻不合法的命名。而觸發器存在于單獨的名稱空間，是以可以與表和過程具有相同的名稱，隻需要確定在一個方案下面所有的觸發器名稱不同，而不需要擔心與表，子過程和包重名。

觸發器是一個存儲在資料字典中的方案對象，除了觸發器本身要具有一定的通路權限之外，次觸發器的所有者必須對觸發器所引用的對象具有必要的對象特權，而且這些權限必須被直接賦予，而不能通過角色進行給予。

      本文轉自潘闊 51CTO部落格，原文連結：http://blog.51cto.com/pankuo/1630256，如需轉載請自行聯系原作者