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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/51862/viewspace-181499/