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.
Hi Mike,
Nice post! I agree it is too complicated to enforce this kind of rules with triggers. I like to use triggers JUST to update “:new values”, or to decide wether to accept/reject the record, but NEVER to change other records.
How to enforce complex constraints using triggers It’s very well explained, I think, in chapter 11 of the book “Applied Mathematics for Database Professionals” by Hann/Koppelaars.
Regards,
Joaquin