Compound triggers in oracle

Question

I have a compound trigger and in the after statement I have an update to other table that has also a compound trigger, like in the code below:

create or replace
trigger TRIGGER
for insert or update on TABLE
COMPOUND trigger

after STATEMENT is
begin
  update THEOTHERTABLE set VALUE = VALUE + 1 where COD = 1;
end after STATEMENT;
end;

The update is just a simple one to see if works. I want it to fire the trigger on THEOTHERTABLE, but it only fires if the trigger is not compound.

Is this a problem with Oracle compound triggers or just a feature which I am not understanding?

Answer 1

I have attempted to re-create your scenario, and it appears to work fine for me. So I think you should look again at your implementation. Look for the differences between what you have coded and what follows here, and perhaps that is where the answer lies.

Here are my triggers

SQL> create or replace
  2  trigger t1_compound
  3  for insert or update on t1
  4  compound trigger
  5
  6      after statement is
  7      begin
  8          update t2 set t1_id = nvl(t1_id,0) + 1 where cod = 12;
  9      end after statement;
 10  end;
 11  /

Trigger created.

SQL>
SQL> create or replace
  2  trigger t2_compound
  3  for insert or update on t2
  4  compound trigger
  5
  6      after statement is
  7      begin
  8          update t3 set t2_id = nvl(t2_id,0) + 1 where cod = 12;
  9      end after statement;
 10  end;
 11  /

Trigger created.

SQL>

... here is the test data ...

SQL> select id, cod from t1
  2  /

        ID        COD
---------- ----------
         1         12

SQL> select id, cod, t1_id from t2
  2  /

        ID        COD      T1_ID
---------- ---------- ----------
        11         12

SQL> select id, cod, t2_id from t3
  2  /

        ID        COD      T2_ID
---------- ---------- ----------
       111         12

SQL> 

... and this is what happens when I issue an update on the first table ...

SQL> update t1 set dt = sysdate
  2  where id = 1
  3  /

1 row updated.

SQL> select id, cod, t1_id from t2
  2  /

        ID        COD      T1_ID
---------- ---------- ----------
        11         12          1

SQL> select id, cod, t2_id from t3
  2  /

        ID        COD      T2_ID
---------- ---------- ----------
       111         12          1

SQL>

Answer 2

i don't think it should work...it would be better if you would create a procedure for THEOTHERTABLE and call that procedure from this trigger.

Answer 3

It works if you do it after compiling the trigger but if you try it again, even with other data, it won't update THEOTHERTABLE