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.