<rt id="bn8ez"></rt>
<label id="bn8ez"></label>

  • <span id="bn8ez"></span>

    <label id="bn8ez"><meter id="bn8ez"></meter></label>

    小菜毛毛技術分享

    與大家共同成長

      BlogJava :: 首頁 :: 聯系 :: 聚合  :: 管理
      164 Posts :: 141 Stories :: 94 Comments :: 0 Trackbacks

    一則父子表下trigger拋出ORA-04091異常的變通處理

       trigger拋出ORA-04091異常,無非是當前trigger下的事務access了一mutating table,比較常見的就是trigger訪問了自身上的表.在一個指定on delete cascade模式下的父子表中,trigger中如果有對其相關的父/子表的訪問,依然會拋出ORA-04091.這是比較隱性的.

       拿oracle的示例表emp和dept來做這個試驗.
       dept的表結構如下:

    create table DEPT
        
    (DEPTNO NUMBER(2) not null primary key,
        
    DNAME  VARCHAR2(14),
        
    LOC    VARCHAR2(13));

        emp表結構如下:

    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是一對父子表,關聯column為DEPTNO.

        接下來創建1個 table:emp_log 和1個語句級 trigger: emp_del_trg.取一個
    最簡單的業務功能,emp_del_trg的作用就是當表emp記錄被刪除的時候,觸發器將刪除的記錄的 EMPNO,DNAME和刪除時間寫入到emp_log中,當子表依賴的父表相關記錄刪除的時候,emp_log不做處理.
        表emp_log的結構如下:

    create table EMP_LOG
        
    (ENAME VARCHAR2(20),
        
    DNAME VARCHAR2(20),
        
    DATES DATE );

        觸發器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;

        來看看這個觸發器是否能正常工作,首先刪除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 發生了變化, 觸發器/函數不能讀它
    ORA-06512: 在 “KEVINYUAN.EMP_DEL_TRG”, line 2
    ORA-04088: 觸發器 ‘KEVINYUAN.EMP_DEL_TRG’ 執行過程中出錯

        ORA-04091錯誤如約而至.不難解釋,因為父子表指定了級聯刪除,刪除dept的記錄
    從而引起刪除emp表上的相應數據,然后觸發了emp_del_trg,由于trigger里有對dept的訪問,對當前事務說,dept就是一個mutating table,這是不被允許的.

        如何來解決這個問題而實現這個簡單的業務邏輯功能呢?當然,從表結構邏輯設計上來講,可以將dept表上的dname字段add到emp表,或者不要顯式的指定references,用程序來維護數據的完整性和約束,然后調整業務代碼.最直接的方法,在trigger中聲明一個ora-04091的exception,對此異常不做處理,也可完成目的。

        我們不妨做一下變通處理.將行級級觸發器變通成語句級觸發器.看下面的處理.

        1.創建一個package:emp_pkg .

    1. CREATE OR REPLACE PACKAGE emp_pkg AS
    2.     /* ----------------------------------
    3.        --Author:Kevin.yuan
    4.        --create_time: 2008 -07-01
    5.     ---------------------------------- */ 
    6.      TYPE crArray IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
    7.      oldRows crArray; --accept the old values of emp
    8.      oldEmpty crArray;  --initialize values
    9.      END emp_pkg;

        2.創建一個語句級trigger,用來觸發trigger的時候清空初始化數據.

    1. CREATE OR REPLACE TRIGGER emp_bd_st BEFORE DELETE ON emp
    2.     /*----------------------------------
    3.       Author:Kevin.yuan
    4.       create_time: 2008 -07-01
    5.     ---------------------------------- */ 
    6.      BEGIN
    7.        emp_pkg.oldRows := emp_pkg.oldEmpty;
    8.      END emp_bd_st;

        3.創建一個行級trigger:emp_d,問題的核心和關鍵就在這里,這個trigger
    并不參與業務邏輯,只是將觸發到的數據載入到emp_pkg.oldRows記錄表里面去.

    1. CREATE OR REPLACE TRIGGER emp_d AFTER DELETE ON emp FOR EACH ROW
    2.     /* ----------------------------------
    3.        --Author:Kevin.yuan
    4.        --create_time: 2008 -07-01
    5.     ---------------------------------- */ 
    6.     DECLARE
    7.     --ct is the position of the deleted records
    8.     ct INTEGER := emp_pkg.oldRows.COUNT + 1;
    9.     BEGIN
    10.      emp_pkg.oldRows(ct).ename := :OLD.ename;
    11.      emp_pkg.oldRows(ct).deptno := :OLD.deptno;
    12.     END emp_d;

        4.創建一個語句級trigger:emp_d_st,前面3步都是為這一步服務的,這一步
    真正參與業務邏輯處理.

    1. CREATE OR REPLACE TRIGGER emp_d_st AFTER DELETE ON emp
    2.     /* ----------------------------------
    3.        --Author:Kevin.yuan
    4.        --create_time: 2008 -07-01
    5.      ---------------------------------- */ 
    6.     DECLARE
    7.     BEGIN
    8.     FOR i IN 1 .. emp_pkg.oldRows.COUNT LOOP
    9.     INSERT INTO emp_log
    10.       (ename, dname, dates)
    11.       select emp_pkg.oldRows(i) .ename, dname, sysdate
    12.         from dept
    13.        WHERE deptno = emp_pkg.oldRows(i).deptno;
    14.     END LOOP;
    15.     END emp_d_st;

        來看一下變通后的觸發器是否滿足我們的業務要求:

    SQL> alter trigger emp_del_trg disable;

    Trigger altered

        刪除emp數據

    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數據.

    SQL> delete from dept;

    4 rows deleted

    SQL> select * from emp_log;

    ENAME DNAME DATES
    ——————– ——————– ———–

       至此,目的實現.由于用了多于常規數量的觸發器,對系統性能會造成一定影響,而且,無疑會加重系統后期業務維護負擔,因此,良好的數據庫邏輯設計和代碼編寫思路是很必要的,否則,只能走另外一些路徑,不過,這個由行級觸發器變語句級別觸發器的思路,還是有必要的,當trigger中無法避免的需要access自身表的時候,這無疑是個可以借鑒的解決方案。

        THE END;




    另外一種方法:
    在declare中加入語句PRAGMA AUTONOMOUS_TRANSACTION;

    最后再提交操作方法,此方法的缺點是觸發器是一個事務,外
    面程序又是一個事務
    posted on 2009-12-04 00:04 小菜毛毛 閱讀(778) 評論(0)  編輯  收藏 所屬分類: 數據庫
    主站蜘蛛池模板: 亚洲婷婷在线视频| 久久亚洲精品人成综合网| 亚洲中文字幕无码爆乳app| 57pao一国产成永久免费| 亚洲AV无码无限在线观看不卡| 久久综合九色综合97免费下载| 亚洲色中文字幕无码AV| a级在线免费观看| 国产成人A人亚洲精品无码| 亚洲高清免费在线观看| 午夜免费福利视频| 亚洲嫩草影院在线观看| 卡1卡2卡3卡4卡5免费视频| 亚洲一区二区女搞男| a级毛片在线免费看| 青青草原精品国产亚洲av| 91精品免费国产高清在线| 亚洲中文精品久久久久久不卡| 国产成人高清精品免费鸭子| 国产亚洲精品2021自在线| 亚洲人成无码网WWW| 暖暖免费在线中文日本| 亚洲乱码卡一卡二卡三| 日韩电影免费在线| 中文字幕在线视频免费观看| 亚洲激情中文字幕| 亚洲免费一区二区| 久久精品国产亚洲av日韩| 毛片免费观看的视频在线| 深夜福利在线免费观看| 国产精品亚洲а∨无码播放| 无码国产精品一区二区免费 | 成人毛片18女人毛片免费视频未 | 蜜臀91精品国产免费观看| 思思久久99热免费精品6| 久久精品国产亚洲av麻豆| 一本久久免费视频| 亚洲一区二区三区日本久久九| 妞干网手机免费视频| 久久高潮一级毛片免费| 亚洲人xxx日本人18|