Dec 092013
 

A compound trigger curiousity I found recently:

begin
  forall triggers in 
      compound.first .. compound.last
    insert into only_fire_once (
      for_the_statement
    ) values (
      compound(triggers)
    );

  forall triggers in 
      compound.first .. compound.last
    update firing
    set    trigger_execution = 
             'once per array entry'
    where  we_equal_the = compound(triggers);
end;

For some reason, compound triggers only fire once for the statement when using forall .. insert (regardless of number of records in the array), but once per array entry for forall .. update statements! For further discussion and examples, keep reading.

(I ran this on 11.2.0.3, I’ve not checked in other versions)

create table only_fire_once (
  for_the_statement varchar2(10)
);
create table firing (
  trigger_execution varchar2(20),
  we_equal_the      integer
);
 
create or replace trigger comp_ins_trig
for insert on only_fire_once compound trigger
 
  chg dbms_sql.number_table;
 
  before each row is
  begin
 
    chg(chg.count + 1) := chg.count + 1;
   
  end before each row;
 
  after statement is
  begin
 
    dbms_output.put_line(
      'Set for ' || chg.count || ' rows');
         
  end after statement;
 
end comp_ins_trig;
/
show errors

create or replace trigger comp_upd_trig
for update on firing compound trigger
 
  chg dbms_sql.number_table;
 
  before each row is
  begin
 
    chg(chg.count + 1) := chg.count + 1;
   
  end before each row;
 
  after statement is
  begin
 
    dbms_output.put_line(
      'Set for ' || chg.count || ' rows');
         
  end after statement;
 
end comp_upd_trig;
/
show errors
 
insert into firing
  select rownum, rownum from dual
  connect by level <= 10;

-- notice how this outputs 11 values, 
-- one for the insert of 10 rows, 
-- one for each update of 10 rows
declare
  type nums is table of integer;
  compound nums := 
    nums(1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
begin
  forall triggers in 
      compound.first .. compound.last
    insert into only_fire_once (
      for_the_statement
    ) values (
      compound(triggers)
    );

  forall triggers in 
      compound.first .. compound.last
    update firing
    set    trigger_execution = 
             'once per array entry'
    where  we_equal_the = compound(triggers);
end;
/

drop table only_fire_once purge;
drop table firing purge;

I found the documentation for FORALL performance when using triggers a bit confusing – as Dom Brooks discusses. So I’m not really sure why the trigger fires multiple times for the update, but only once for the insert. Anyone else got any ideas?

  4 Responses to “Compounding the problem for updates”

  1. I find the lack of consistency disturbing.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)