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.