Oracle – for when it was like that when you got there

Main menu

Post navigation

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 :

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;
/

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;
/

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.