Learning is not a spectator sport

April 24, 2014

Really dumb triggers

Filed under: Uncategorized — connormcdonald @ 1:36 pm

Some people hate triggers, some people love triggers…

I am not that opinionated on them in either direction, but one thing I do hate, whether it be a trigger or not, is dumb code.  And today’s post just happens to be about dumb code in a trigger.

Consider this simple trigger (you see these everywhere pre 12c):

CREATE OR REPLACE TRIGGER MY_TRG
  BEFORE INSERT ON MY_TABLE 
  FOR EACH ROW
BEGIN
   SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_PK_COL FROM DUAL;
END;
/

Seems harmless enough…and I’m not talking about whether we should be using ":new.col := seq.nextval", because under the covers it will do a select-from-dual anyway.

The reason the code is really dumb…is you are totally screwed if you need to some data maintenance…

Consider the following scenario:

Customer: "Last week, I accidentally deleted MY_PK_COL = 10, here’s what the row should look like, can you please put it back?"

Your response is simple…."No I cant".

Why ? Because you can never, ever re-insert MY_PK_COL = 10 if the sequence has advanced past 10.  All of your options are nasty…

a) reset the sequence value ?  What of other transactions taking place?

b) disable the trigger ? ditto.

Now people will get up on their soap box and say "Its a surrogate key, the value should be meaningless, it shouldn’t matter what it is" etc etc…and I admire your tenacious grip on the religious argument.  But that’s like saying "All databases should have referential integrity constraints!"…Well duh, but that’s not how the real world is :-(

Its just a dumb way of coding.  If you really need these kinds of triggers (hint: you don’t), then at least code them defensively:

CREATE OR REPLACE TRIGGER MY_TRG
  BEFORE INSERT ON MY_TABLE 
  FOR EACH ROW
  when ( new.MY_PK_COL is null )
BEGIN
   SELECT MY_SEQ.NEXTVAL INTO :NEW.MY_PK_COL FROM DUAL;
END;
/

so at least you’re not clobbering someone’s data. 

About these ads

4 Comments »

  1. Great point, this is similar to why I dislike triggers for maintaing application data.

    I’ve worked on a couple of systems that have triggers on transaction tables. These then updated customer account balances etc. in separate tables.

    These lead to similar issues to then one you describe if you need to do some (large scale) data maintenance (e.g. correcting balances). Disabling the triggers to do the update means you need downtime; adding new transactions in can work but can take a very long time to run.

    At least these are generally slightly easier to overcome than re-inserting the id situation here!

    Comment by Chris Saxon — April 24, 2014 @ 8:00 pm

  2. Allowing any application or user to arbitrarily assign their primary key isn’t any better, in my humble opinion. At some point, the sequence will cross the arbitrary values inserted, so you’re setting yourself up for failed inserts due to uniqueness violations. I deal with “Oops I deleted a record” situations infrequently enough that I’d prefer inserting the record & updating the primary key back to its original value. As with anything, it depends.

    Incidentally, I’ll take the benefits of foreign key constraints in spite of the pain they bring when copying data between environments. That said, I’m not going to go creating them on every table as I’m sure it’d break numerous applications that were written when the Earth was still flat (or before a full-time DBA was employed).

    Comment by T. J. Kiernan — April 24, 2014 @ 11:36 pm

  3. Please note – – > the author’s realisation that ‘…Well duh, but that’s not how the real world is ‘ — > The design of the data model should be revised so that the logical / physical deletes can be accommodated without issues that arise in ‘real world’. To ‘delete’ some record from the database is not entirely valid and why should a record get deleted (in any case). This is an issue of design and not the fault of any trigger that has been specified previously. It is not a simple matter to allow for flexibility for sake of ‘maintenance’ as there are and may well be legitimate reasons that the model had been designed with a concrete trigger as outline.

    Comment by Sue — April 24, 2014 @ 11:53 pm

  4. […] the previous post, I pontificated about triggers that "lock you in" to having them fire, which can create […]

    Pingback by Dumb triggers part 2 – session level control | Learning is not a spectator sport — April 26, 2014 @ 12:10 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: