Compound Triggers - do they work (properly)?

From: Steve Baldwin <>
Date: Wed, 2 Dec 2009 14:52:41 -0600
Message-ID: <>

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

        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

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

  procedure do_ins(i_c1 in varchar2) is
    insert into sb_t(c1) values (i_c1);

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 - Production on Wed Dec 2 14:49:20 2009

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

Connected to:
Oracle Database 11g Release - 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?



Received on Wed Dec 02 2009 - 14:52:41 CST

Original text of this message