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

Main menu

Post navigation

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.

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” :

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.

One thought on “Triggers are bad m’kay – more Mutant Madness”

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.