Triggers are bad m’kay – more Mutant Madness

The title of this post should be read in the style of Mr Mackey from South Park.
Just to be clear at this point – I do not subscribe to this view. Triggers, like most things Oracle, have their place. It’s knowing where and when to use them that’s the trick.
In a recent post, I outlined the solution to the Mutating Table issue that is offered by Compound Triggers.
Unfortunately, despite watching all of the X-Men movies, I failed to realise that Mutants tend to be quite resilient.
Joaquin pointed out that this trigger would not work as expected when a statement contained multiple updates.
At this point, let’s recap.

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

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


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


So far, so good.
Now let’s apply Joaquin’s code to this scenario :

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 05-JUN-10
1 ORDER SEND 05-JUN-10
2 ORDER PACK 05-JUN-10
3 rows selected.


If we want to cater for statements containing multiple inserts, we could just change the trigger to store the row-level values in a PL/SQL table :

CREATE OR REPLACE TRIGGER ms_aiuc FOR INSERT ON misc_statuses
COMPOUND TRIGGER

TYPE typ_ms IS TABLE OF misc_statuses%ROWTYPE INDEX BY PLS_INTEGER;
tbl_ms typ_ms;

l_index PLS_INTEGER := 0;
AFTER EACH ROW IS
BEGIN
    IF :new.domain_name = 'ORDER' THEN
        l_index := l_index + 1;
        tbl_ms( l_index).key_value := :new.key_value;
        tbl_ms( l_index).domain_name := :new.domain_name;
        tbl_ms( l_index).status := :new.status;
    END IF;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
    IF l_index > 0 THEN
        FOR i IN 1..tbl_ms.COUNT LOOP
            UPDATE misc_statuses
            SET end_date = SYSDATE
            WHERE key_value = tbl_ms(i).key_value
            AND domain_name = tbl_ms(i).domain_name
            AND end_date IS NULL
            AND status != tbl_ms(i).status;
        END LOOP;
  END IF;  
END AFTER STATEMENT;
END;
/

That ought to do it…


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 05-JUN-10 05-JUN-10
1 ORDER SEND 05-JUN-10 05-JUN-10
2 ORDER PACK 05-JUN-10


Oh.

To solve this, we really need to take a step back.
Remember, our Orders can have one of four statuses – Pick, Pack, Prep and Send.
It’s probably useful to set out the business rules that the system is supposed to be supporting in this instance.
An Order must go through these stages in that order. For example, an Order cannot go from Pick directly to Send.
Back comes our Designer, pausing only to declare “Oh darling, Relational Theory is so 1970’s” :

CREATE TABLE status_progressions(
  domain_name VARCHAR2(30),
  status_order NUMBER(1),
  status VARCHAR2(30));
  
INSERT INTO status_progressions( domain_name, status_order, status)
VALUES( 'ORDER', 1, 'PICK');

INSERT INTO status_progressions( domain_name, status_order, status)
VALUES( 'ORDER', 2, 'PACK');

INSERT INTO status_progressions( domain_name, status_order, status)
VALUES( 'ORDER', 3, 'PREP');

INSERT INTO status_progressions( domain_name, status_order, status)
VALUES( 'ORDER', 4, 'SEND');

commit;

Now we can change the trigger so that the query to end-date the status is more specific :

CREATE OR REPLACE TRIGGER ms_aiuc FOR INSERT ON misc_statuses
COMPOUND TRIGGER

TYPE typ_ms IS RECORD (
    key_value misc_statuses.key_value%TYPE,
    domain_name misc_statuses.domain_name%TYPE,
    status misc_statuses.status%TYPE);
    
TYPE typ_ms_tbl IS TABLE OF typ_ms INDEX BY PLS_INTEGER;
 
tbl_ms typ_ms_tbl;

l_index PLS_INTEGER := 0;
AFTER EACH ROW IS
BEGIN
    IF :new.domain_name = 'ORDER' THEN
        l_index := l_index + 1;
        tbl_ms( l_index).key_value := :new.key_value;
        tbl_ms( l_index).domain_name := :new.domain_name;
        tbl_ms( l_index).status := :new.status;
    END IF;
END AFTER EACH ROW;

AFTER STATEMENT IS

l_status_order status_progressions.status_order%TYPE;

CURSOR c_status_order( cp_status status_progressions.status%TYPE) IS
    SELECT status_order
    FROM status_progressions
    WHERE status = cp_status;

l_prev_status status_progressions.status%TYPE;

CURSOR c_prev_status ( cp_status_order status_progressions.status_order%TYPE) IS
    SELECT status
    FROM status_progressions
    WHERE domain_name = 'ORDER'
    AND status_order = cp_status_order;

e_invalid_status EXCEPTION;
BEGIN
    IF l_index > 0 THEN
        FOR i IN 1..tbl_ms.COUNT LOOP 
            OPEN c_status_order( tbl_ms(i).status);
            FETCH c_status_order INTO l_status_order;
            CLOSE c_status_order;
            IF l_status_order > 1 THEN
                --
                -- Now find the previous status in the progression
                --
                OPEN c_prev_status( l_status_order - 1);
                FETCH c_prev_status INTO l_prev_status;
                CLOSE c_prev_status;
                --
                -- End the previous status record. If we don't update
                -- any rows then this status isn't in the progression so
                -- error.
                UPDATE misc_statuses
                SET end_date = SYSDATE
                WHERE key_value = tbl_ms(i).key_value
                AND domain_name = tbl_ms(i).domain_name
                AND status = l_prev_status;
                IF SQL%ROWCOUNT = 0 THEN
                    RAISE e_invalid_status;
                END IF;
            END IF;
        END LOOP;
    END IF;
EXCEPTION 
    WHEN e_invalid_status THEN
        RAISE_APPLICATION_ERROR(-20000, 'Invalid status progression');
END AFTER STATEMENT;
END;
/

Now, let’s try Joaquin’s code again – having changed the statuses so that it conforms with the progression rules we’ve just implemented.


INSERT INTO misc_statuses SELECT '1', 'ORDER', 'PICK', sysdate, NULL FROM DUAL
UNION ALL SELECT '1', 'ORDER', 'PACK', sysdate, NULL FROM DUAL
UNION ALL SELECT '2', 'ORDER', 'PICK', sysdate, NULL FROM DUAL;

SELECT * FROM misc_statuses
/
KEY_VALUE DOMAIN_NAME STATUS START_DATE END_DATE
--------- ----------- ------ ---------- --------
1 ORDER PICK 09-JUN-10 09-JUN-10
1 ORDER PACK 09-JUN-10
2 ORDER PICK 09-JUN-10


That’s an awful lot of messing about to implement a fairly simple business rule. Of course, this example does not account for any circumstances where an order may need to revert to a previous status.
OK, that’s not the point of this post, but it does all show that, when designing an application, it’s useful to remember this :
RDBMS – Relational Database Management System – the clue’s in the name.