Loading...
[-]

Compound triggers and mutating tables

Source: trigger.sql

Oracle 11gR1 iconIf you are in a mutating table situation where your trigger needs to read from the same table that it is firing on your only real option before Oracle 11g was a multi trigger solution with a muddle of row and statement triggers communicating via a package. But now Oracle 11g comes through with a great new feature: compound triggers which neatly solve the problem … or do they. Well they purport to do so but it isn’t all smooth sailing: Beware the compound trigger. But I figured – that’s 11.1.0.6 whereas I’m using 11.1.0.7 and those particular failure modes don’t apply to my situation so I’ll be fine. Not exactly. What I have is a normal after statement trigger firing on the “banner” column and modifying data in the “changed” column and I need to fire a second trigger on that second column. What I saw was that the compound trigger worked properly once and then not again until it was recompiled.

trg_after => fires on column “banner” and modifies column “changed”.
trg_compound_row => fires on column “changed”
trg_simple_row => fires on same criteria as compound trigger for comparative purposes

The test scenario is:
0) Create table + triggers
1) Update table to see triggers firing then rollback to reset
2) Do step 1 again
3) Recompile trigger
4) Do step 1 twice
5) Drop table

SQL> set echo on
SQL> @trigger.sql
SQL> SET SERVEROUT ON LINESIZE 200
SQL>
SQL> CREATE table trigger_test AS
  2  SELECT rownum id, banner, 'A' changed FROM v$version;

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER trg_after
  2      AFTER UPDATE OF banner ON trigger_test
  3  BEGIN
  4      dbms_output.put_line('Update');
  5      UPDATE trigger_test SET changed = 'B'
  6       WHERE id = 1;
  7  END;
  8  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER trg_compound_row
  2    FOR UPDATE OF changed ON trigger_test
  3  COMPOUND TRIGGER
  4
  5  AFTER EACH ROW IS BEGIN
  6      dbms_output.put_line('Compound fired <== Should happen every time');
  7  END AFTER EACH ROW;
  8
  9  END trg_compound_row;
 10  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER trg_simple_row
  2      AFTER UPDATE OF changed ON trigger_test
  3      FOR EACH ROW
  4  BEGIN
  5      dbms_output.put_line('Simple fired');
  6  END;
  7  /

Trigger created.

SQL> SELECT * FROM trigger_test;

        ID BANNER                                                                           C
---------- -------------------------------------------------------------------------------- -
         1 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production     A
         2 PL/SQL Release 11.1.0.7.0 - Production                                           A
         3 CORE 11.1.0.7.0      Production                                                       A
         4 TNS for Solaris: Version 11.1.0.7.0 - Production                                 A
         5 NLSRTL Version 11.1.0.7.0 - Production                                           A

SQL>
SQL> UPDATE trigger_test SET banner = banner || ' ' WHERE id = 2;
Update
Compound fired <== Should happen every time
Simple fired

1 row updated.

SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> UPDATE trigger_test SET banner = banner || ' ' WHERE id = 2;
Update
Simple fired

1 row updated.

SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> ALTER TRIGGER trg_compound_row COMPILE;

Trigger altered.

SQL>
SQL> UPDATE trigger_test SET banner = banner || ' ' WHERE id = 2;
Update
Compound fired <== Should happen every time
Simple fired

1 row updated.

SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> UPDATE trigger_test SET banner = banner || ' ' WHERE id = 2;
Update
Simple fired

1 row updated.

SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> DROP TABLE trigger_test;

Table dropped.

SQL>

So the lesson is clear, as Dominic Brooks already stated: Beware the compound trigger.

2 Comments

  1. Posted August 3, 2010 at 21:42 | Permalink

    Metalink doc 1050868.1 “documents” some problems around multiple compound triggers. This probably falls into the same category (i.e. multiple triggers albeit not multiple compound triggers).

    That doc says “it is not yet known what code change introduced these problems but customers had success by applying a couple one-off fixes that seem to resolve most problems. ”

    Reassurring?

    However, doc id 943679.1 relates to bug 8463344 / 8850303.
    Whilst the title mentions the Itanium platform, the body of the doc says that this applies to any platform.

    The Symptoms?
    “trigger produces output as expected however on some other operating systems, the trigger stops firing after first row.”

    Solution – patch 8850303.

    Sounds like a good match.

    I’m back on a 9i system at the moment but upgrading to 11gR2 shortly. I look forward to seeing a proper, thorough and working implementation of compound triggers on that version.

    Cheers,
    Dominic

  2. Posted August 4, 2010 at 09:53 | Permalink

    Thanks Dominic. We’ll be moving on to 11gR2 soon too. Hopefully things will be better there.

Post a Comment

Your email is never shared. Required fields are marked *

*
*