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

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