RE: Compound Triggers - do they work (properly)?

SQL> _at_ compoundtrigger  

Table created.    

Trigger created.    

Trigger created.  

sb_t before statement (normal)

sb_t before statement (compound)

sb_t before statement (normal)

sb_t before statement (compound)  

PL/SQL procedure successfully completed.    

Table dropped.  

Andre  

Van: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] Namens Toon Koppelaars
Verzonden: donderdag 3 december 2009 6:43 Aan: stbaldwin_at_multiservice.com
CC: oracle-l_at_freelists.org
Onderwerp: Re: Compound Triggers - do they work (properly)?  

Works fine in 11.1.0.6:

Connected to:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production

Session altered.

SQL> edit t.sql

SQL> start t

Table created.

Trigger created.

Trigger created.

sb_t before statement (normal)
sb_t before statement (compound)
sb_t before statement (normal)
sb_t before statement (compound)

PL/SQL procedure successfully completed.

Table dropped.

On Wed, Dec 2, 2009 at 9:52 PM, Steve Baldwin <stbaldwin_at_multiservice.com> wrote:

Am I imagining this, or is there a *major* problem with compound triggers?

Consider this ...

[stbaldwin_at_opbld06 ~]$ cat sb1.sql
set serveroutput on size 1000000
create table sb_t(c1 varchar2(10));

create or replace trigger sb_t_ct01

   for insert on sb_t
   compound trigger
   --
   before statement is
   begin

       dbms_output.put_line('sb_t before statement (compound)');    end before statement;
end sb_t_ct01;
/

create or replace trigger sb_t_bi01

   before insert on sb_t
begin

   dbms_output.put_line('sb_t before statement (normal)'); end;
/

declare
 procedure do_ins(i_c1 in varchar2) is
 begin
   insert into sb_t(c1) values (i_c1);
 end;
begin
 do_ins('aaa');
 do_ins('bbb');
 rollback;
end;
/

drop table sb_t;

As you can see, I create a 'normal' before insert statement level trigger, and a compound trigger that has only a before statement timing point.

I then execute a small anonymous block that calls an insert twice. I would expect to see four lines of output - 1 from each trigger * 2 executions.

Here's the output ...

[stbaldwin_at_opbld06 ~]$ sqlplus sb_test/sb_test

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Dec 2 14:49:20 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option

SQL> _at_sb1

Table created.

Trigger created.

Trigger created.

sb_t before statement (normal)
sb_t before statement (compound)
sb_t before statement (normal)

PL/SQL procedure successfully completed.

Table dropped.

As you can see, the compound trigger is only executed once.

Is this something stupid I'm doing or a possible bug?

Thanks,

Steve

--
http://www.freelists.org/webpage/oracle-l






-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13



--
http://www.freelists.org/webpage/oracle-l