ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10.1.0.3delete触发器的bug

10.1.0.3delete触发器的bug

原创 Linux操作系统 作者:orchidllh 时间:2008-02-22 09:57:12 0 删除 编辑

今天应用反映某个测试库上的触发器没有生效,而在线环境则是正确的。
检查该触发器,是一个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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 只好稍安勿躁
请登录后发表评论 登录
全部评论

注册时间:2008-02-21

  • 博文量
    180
  • 访问量
    843935