Compound Triggers – Managing the Mutant Menace

Now I’ve got 11g up and running, I’ve finally had the chance to mess about with some of the new features.
Anyone who has done any amount of work with Oracle triggers will be familiar with the dreaded ORA-04091 mutating table error.
You’ll be relieved to hear that I’ve managed to resist the temptation to start talking about X-Men. The medication must be working.

Instead, here’s a quick demo of an 11g way of getting around this particular problem.

Let’s start with one of those horrible generic tables that someone must have thought was a good idea at the time :

CREATE TABLE misc_statuses(
    key_value VARCHAR2(10),
    domain_name VARCHAR2(30),
    status VARCHAR2(30),
    start_date date,
    end_date date);

INSERT INTO misc_statuses( key_value, domain_name, status, start_date, end_date)
VALUES( '1', 'ORDER', 'PICK', '01-APR-10', NULL);

The sample record is for an order. In this application, an order can have one of four statuses – PICK, PACK, PREP and SEND.
An order may only have one current status and so when it is assigned a new status, any previous status must be end-dated.
The same someone who came up with the generic status table has also read somewhere that AFTER ROW triggers are more efficient than BEFORE ROW triggers and so mandated that all row-level table triggers must be AFTER, not BEFORE.
As a result, the trigger to enforce the rule about order statuses is as follows :

CREATE OR REPLACE TRIGGER ms_aiu AFTER INSERT ON misc_statuses
FOR EACH ROW
BEGIN
    IF :new.domain_name = 'ORDER' THEN
      UPDATE misc_statuses
      SET end_date = SYSDATE
      WHERE key_value = :new.key_value
      AND domain_name = :new.domain_name
      AND end_date IS NULL
      AND status != :new.status;
    END IF;  
END;
/

Seems straightforward enough. Let's test it :

INSERT INTO misc_statuses(key_value, domain_name, status, start_date, end_date)
VALUES( '1', 'ORDER', 'PACK', '01-APR-10', NULL);
ORA-04091: table MIKE.MISC_STATUSES is mutating, trigger/function may not see it
ORA-06512: at "MIKE.MS_AIU", line 3
ORA-04088: error during execution of trigger 'MIKE.MS_AIU'

Up until now, a common workaround in situations like this has been for the row-level trigger to be used to write the :NEW values to a PL/SQL table defined in a database package.
The PL/SQL table is then read by an AFTER STATEMENT trigger on the table. So, as well as having a poorly designed table structure, you also have two triggers and a database package to enforce this simple rule.
At this point, it's customary to pause for a moment and reflect that the poor, misguided souls who overdo generic table design such as that illustrated here, should not be villified. They should be treated with kindness and shown the error of their ways...preferrably with the aid of a blunt instrument.
On the plus side, in 11g we have Compound Triggers at our disposal, so we can overcome this issue in one fell swoop :

CREATE OR REPLACE TRIGGER ms_aiuc FOR INSERT ON misc_statuses
COMPOUND TRIGGER
  l_key_value misc_statuses.key_value%TYPE;
  l_domain_name misc_statuses.domain_name%TYPE;
  l_status misc_statuses.status%TYPE;
AFTER EACH ROW IS
BEGIN
  IF :new.domain_name = 'ORDER' THEN
    l_key_value := :new.key_value;
    l_domain_name := :new.domain_name;
    l_status := :new.status;
  END IF;  
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
  IF l_key_value IS NOT NULL THEN
    UPDATE misc_statuses
    SET end_date = SYSDATE
    WHERE key_value = l_key_value
    AND domain_name = l_domain_name
    AND end_date IS NULL
    AND status != l_status;
  END IF;  
END AFTER STATEMENT;
END;
/

Now let's try that again.

insert into misc_statuses values( '1', 'ORDER', 'PACK', '01-APR-10', NULL);
1 row created

So there you have it – compound triggers...for when you haven't got a stick with nails in.

About these ads

2 thoughts on “Compound Triggers – Managing the Mutant Menace

  1. It does not work with multi-insert:

    insert into misc_statuses SELECT ’1′, ‘ORDER’, ‘PACK’, sysdate, NULL FROM DUAL
    UNION ALL SELECT ’1′, ‘ORDER’, ‘SEND’, sysdate, NULL FROM DUAL
    UNION ALL SELECT ’2′, ‘ORDER’, ‘PACK’, sysdate, NULL FROM DUAL;

    select * from misc_statuses;
    KEY_VALUE DOMAIN_NAME STATUS START_DATE END_DATE
    ——— ———– —— ——————- ——–
    1 ORDER PACK 24/05/2010 10:05:40
    1 ORDER SEND 24/05/2010 10:05:40
    2 ORDER PACK 24/05/2010 10:05:40

    3 rows selected.

    • Joacquin,

      It’s a good point.
      I deliberately avoided using arrays to keep the example simple. Also, I can’t think of too many real-world situations where this would occur for a single Order.
      Having checked however, it doesn’t work as expected if you use a PL/SQL table either.
      The answer would appear to be to sort out the design. I’ll post on this again shortly.

      Mike

      Update : the new post is here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s