10.1.0.3delete觸發器的bug

今天應用反映某個測試庫上的觸發器沒有生效，而線上環境則是正確的。
檢查該觸發器，是一個before delete觸發器，有where條件，測試的確是沒有觸發，觸發器很簡單，如下：
CREATE OR REPLACE TRIGGER TR_D_****_TAB
AFTER DELETE ON ****_TAB
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
when (old.column_a <> old.column_b)
BEGIN
 raise_application_error(-20096,'error:you will delete record where column_a <> column_b');
END TR_D_****_TAB;

將when條件中的內容挪到trigger中問題就解決了：
CREATE OR REPLACE TRIGGER TR_D_****_TAB
BEFORE DELETE ON ****_TAB
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
 if :old.column_a <> :old.column_b then
  raise_application_error(-20096,'error:you will delete record where column_a <> column_b');
 end if;
END TR_D_****_TAB;   

檢查線上環境，原來寫在when中的觸發器也是可以正確觸發的。
檢查兩個資料庫的版本，測試環境是10.1.0.3，線上環境是10.2.0.3，於是基本判定是這個版本的bug，在metalink上找到這個bug的對應資訊：

https://metalink.oracle.com/metalink/plsql/f?p=130:14:7336324387296867284::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,3571946.8,1,0,1,helvetica

Subject:  Bug 3571946 - DELETE trigger does not fire on delete with index scan
  Doc ID:  Note:3571946.8 Type:  PATCH
  Last Revision Date:  10-AUG-2005 Status:  PUBLISHED
 Click here for details of sections in this note.

Bug 3571946  DELETE trigger does not fire on delete with index scan
 This note gives a brief overview of bug 3571946.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 9.2.0.4 but < 10.2.0.1 
Versions confirmed as being affected 9.2.0.4 9.2.0.5 10.1.0.2
 
Platforms affected Generic (all / most platforms affected)

 It is believed to be a regression in default behaviour thus:
   Regression introduced in 9.2.0.4

Fixed:
This issue is fixed in 9.2.0.6 (Server Patch Set) 10.1.0.4 (Server Patch Set) 10.2.0.1 (Base Release)
 
Symptoms: Related To: Corruption (Logical)  Triggers

Description
Delete trigger may not fire if there is a WHEN clause in the trigger
and the delete uses INDEX access if the columns present in the WHEN
clause are not present in the index.

Workaround:
  Add a dummy predicate in the WHERE clause that contains the column/s
   in the trigger WHEN clause.

eg:
 create table test_tab1 (col1 number, col2 number);
 create index test_idx on test_tab1(col1);
 Create a DELETE trigger which fires "when(old.col2=1)".

按照bug中描述，只有在按照索引查詢且有when條件的時候，delete觸發器不觸發，有這個bug的資料庫可以將when條件挪到trigger中解決。
測試證實delete時不使用索引，的確是可以觸發的。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/51862/viewspace-181499/，如需轉載，請註明出處，否則將追究法律責任。