RE: Compound Triggers - do they work (properly)?
- From: "Andre van Winssen" <dreveewee@xxxxxxxxx>
- To: <toon@xxxxxxxxxxx>, <stbaldwin@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 Dec 2009 09:27:25 +0100
Same in 11.2.0.1
SQL> @ 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Namens Toon Koppelaars
Verzonden: donderdag 3 december 2009 6:43
Aan: stbaldwin@xxxxxxxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxx>
wrote:
Am I imagining this, or is there a *major* problem with compound triggers?
Consider this ...
[stbaldwin@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@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> @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
--
//www.freelists.org/webpage/oracle-l
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13
Other related posts:
