一則父子表下trigger拋出ORA-04091異常的變通處理
07月 1st, 2008 |post by Kevin.yuan |【轉(zhuǎn)載時(shí)請(qǐng)務(wù)必以超鏈接形式標(biāo)明文章原始出處和作者信息】
trigger拋出ORA-04091異常,無非是當(dāng)前trigger下的事務(wù)access了一mutating table,比較常見的就是trigger訪問了自身上的表.在一個(gè)指定on delete cascade模式下的父子表中,trigger中如果有對(duì)其相關(guān)的父/子表的訪問,依然會(huì)拋出ORA-04091.這是比較隱性的.
拿oracle的示例表emp和dept來做這個(gè)試驗(yàn).
dept的表結(jié)構(gòu)如下:
create table DEPT
(DEPTNO NUMBER(2) not null primary key,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
emp表結(jié)構(gòu)如下:
create table EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
foreign key(deptno) references dept(deptno) on delete cascade);
emp和dept是一對(duì)父子表,關(guān)聯(lián)column為DEPTNO.
接下來創(chuàng)建1個(gè) table:emp_log 和1個(gè)語句級(jí) trigger: emp_del_trg.取一個(gè)
最簡(jiǎn)單的業(yè)務(wù)功能,emp_del_trg的作用就是當(dāng)表emp記錄被刪除的時(shí)候,觸發(fā)器將刪除的記錄的 EMPNO,DNAME和刪除時(shí)間寫入到emp_log中,當(dāng)子表依賴的父表相關(guān)記錄刪除的時(shí)候,emp_log不做處理.
表emp_log的結(jié)構(gòu)如下:
create table EMP_LOG
(ENAME VARCHAR2(20),
DNAME VARCHAR2(20),
DATES DATE );
觸發(fā)器trigger代碼如下:
create or replace trigger emp_del_trg after delete on emp for each row
begin
insert into emp_log
select :old.ename, dname, sysdate from dept where deptno = :old.deptno;
end;
來看看這個(gè)觸發(fā)器是否能正常工作,首先刪除emp的記錄.
SQL> delete from emp where rownum<5;
4 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
SMITH RESEARCH 2008-7-1 18
ALLEN SALES 2008-7-1 18
WARD SALES 2008-7-1 18
JONES RESEARCH 2008-7-1 18
SQL> rollback;
Rollback complete
看似trigger工作正常,刪除dept的記錄呢?
SQL> delete from dept where rownum<3;
delete from dept
ORA-04091: 表 KEVINYUAN.DEPT 發(fā)生了變化, 觸發(fā)器/函數(shù)不能讀它
ORA-06512: 在 “KEVINYUAN.EMP_DEL_TRG”, line 2
ORA-04088: 觸發(fā)器 ‘KEVINYUAN.EMP_DEL_TRG’ 執(zhí)行過程中出錯(cuò)
ORA-04091錯(cuò)誤如約而至.不難解釋,因?yàn)楦缸颖碇付思?jí)聯(lián)刪除,刪除dept的記錄
從而引起刪除emp表上的相應(yīng)數(shù)據(jù),然后觸發(fā)了emp_del_trg,由于trigger里有對(duì)dept的訪問,對(duì)當(dāng)前事務(wù)說,dept就是一個(gè)mutating table,這是不被允許的.
如何來解決這個(gè)問題而實(shí)現(xiàn)這個(gè)簡(jiǎn)單的業(yè)務(wù)邏輯功能呢?當(dāng)然,從表結(jié)構(gòu)邏輯設(shè)計(jì)上來講,可以將dept表上的dname字段add到emp表,或者不要顯式的指定references,用程序來維護(hù)數(shù)據(jù)的完整性和約束,然后調(diào)整業(yè)務(wù)代碼.最直接的方法，在trigger中聲明一個(gè)ora-04091的exception，對(duì)此異常不做處理，也可完成目的。
我們不妨做一下變通處理.將行級(jí)級(jí)觸發(fā)器變通成語句級(jí)觸發(fā)器.看下面的處理.
1.創(chuàng)建一個(gè)package:emp_pkg .
- CREATE OR REPLACE PACKAGE emp_pkg AS
- /* ----------------------------------
- --Author:Kevin.yuan
- --create_time: 2008 -07-01
- ---------------------------------- */
- TYPE crArray IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
- oldRows crArray; --accept the old values of emp
- oldEmpty crArray; --initialize values
- END emp_pkg;
2.創(chuàng)建一個(gè)語句級(jí)trigger,用來觸發(fā)trigger的時(shí)候清空初始化數(shù)據(jù).
- CREATE OR REPLACE TRIGGER emp_bd_st BEFORE DELETE ON emp
- /*----------------------------------
- Author:Kevin.yuan
- create_time: 2008 -07-01
- ---------------------------------- */
- BEGIN
- emp_pkg.oldRows := emp_pkg.oldEmpty;
- END emp_bd_st;
3.創(chuàng)建一個(gè)行級(jí)trigger:emp_d,問題的核心和關(guān)鍵就在這里,這個(gè)trigger
并不參與業(yè)務(wù)邏輯,只是將觸發(fā)到的數(shù)據(jù)載入到emp_pkg.oldRows記錄表里面去.
- CREATE OR REPLACE TRIGGER emp_d AFTER DELETE ON emp FOR EACH ROW
- /* ----------------------------------
- --Author:Kevin.yuan
- --create_time: 2008 -07-01
- ---------------------------------- */
- DECLARE
- --ct is the position of the deleted records
- ct INTEGER := emp_pkg.oldRows.COUNT + 1;
- BEGIN
- emp_pkg.oldRows(ct).ename := :OLD.ename;
- emp_pkg.oldRows(ct).deptno := :OLD.deptno;
- END emp_d;
4.創(chuàng)建一個(gè)語句級(jí)trigger:emp_d_st,前面3步都是為這一步服務(wù)的,這一步
真正參與業(yè)務(wù)邏輯處理.
- CREATE OR REPLACE TRIGGER emp_d_st AFTER DELETE ON emp
- /* ----------------------------------
- --Author:Kevin.yuan
- --create_time: 2008 -07-01
- ---------------------------------- */
- DECLARE
- BEGIN
- FOR i IN 1 .. emp_pkg.oldRows.COUNT LOOP
- INSERT INTO emp_log
- (ename, dname, dates)
- select emp_pkg.oldRows(i) .ename, dname, sysdate
- from dept
- WHERE deptno = emp_pkg.oldRows(i).deptno;
- END LOOP;
- END emp_d_st;
來看一下變通后的觸發(fā)器是否滿足我們的業(yè)務(wù)要求:
SQL> alter trigger emp_del_trg disable;
Trigger altered
刪除emp數(shù)據(jù)
SQL> delete from emp where rownum<3;
2 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
SMITH RESEARCH 2008-7-1 19
ALLEN SALES 2008-7-1 19
SQL> rollback;
Rollback complete
刪除dept數(shù)據(jù).
SQL> delete from dept;
4 rows deleted
SQL> select * from emp_log;
ENAME DNAME DATES
——————– ——————– ———–
至此,目的實(shí)現(xiàn).由于用了多于常規(guī)數(shù)量的觸發(fā)器,對(duì)系統(tǒng)性能會(huì)造成一定影響,而且,無疑會(huì)加重系統(tǒng)后期業(yè)務(wù)維護(hù)負(fù)擔(dān),因此,良好的數(shù)據(jù)庫邏輯設(shè)計(jì)和代碼編寫思路是很必要的,否則,只能走另外一些路徑,不過,這個(gè)由行級(jí)觸發(fā)器變語句級(jí)別觸發(fā)器的思路,還是有必要的，當(dāng)trigger中無法避免的需要access自身表的時(shí)候，這無疑是個(gè)可以借鑒的解決方案。
THE END;
另外一種方法：
在declare中加入語句PRAGMA AUTONOMOUS_TRANSACTION;
最后再提交操作方法，此方法的缺點(diǎn)是觸發(fā)器是一個(gè)事務(wù)，外
面程序又是一個(gè)事務(wù)