|
Something I try to avoid whenever I'm writing a trigger is to not perform a select on the table that the trigger is being created for. That is usually a recipe for disaster. I understand the need for the trigger to use a SEQUENCE in order to set the value of :new.ID. But I don't understand the need to query the existing table to validate the value. That can all be done using CONSTRAINTS and other database constructs.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
As another poster has pointed out, this is a conversion tool. It is assuming that there will be data in the table already before this trigger is invoked, but the value of the sequence may not be correctly set to match the existing data. So, it tries to automatically adjust the sequence to keep it in line with any pre-existing values in the table, which is why it queries the table.
Unfortunately, it does not consider the edge case of an empty table.
|
|
|
|
|
Maybe Oracle is not such known here, so let me give you some more explanations.
With SQL Server or MS Access, we often use "automatic IDs", that is an integer with the "Identity" property (SQL Server) set to true or the "New Values" property set to "increment". When you then insert a row, you do not care for the identifier in the table, the database generates that automatically, and from a program you can query it with SELECT @@IDENTITY .
Oracle cannot do that. You need a NUMBER column, then a "sequence" which will feed the new numbers, and a trigger for the INSERT event, which will call the sequence's NEXTVAL (next new number) and put that into the new row's ID column. From a program, you can query that value with SELECT MYSEQUENCE.CURRVAL FROM DUAL .
The above trigger was automatically generated by Oracle for the migration of an SQL Server database. When you do a migration, (most) tables do already contain some rows, and hence the trigger must be adjusted to the existing values. That's is to be accomplished by the "IF v_newVal = 1 THEN " section.
And that section's code is terrible, and - as proofed above - wrong when the table was still empty. Not only do they call the sequence's NEXTVAL as often as the maximum ID value of the table, in case of a previously empty table the ID value is set to 1 while the sequence's CURRVAL is already 2.
|
|
|
|
|
Thanks for that explanation, cause at first I thought that selecting a max value from a table to get
a new id is a coding horror itself. But your statement explains nearly everything.
(I had to use an Oracle-DB only one time and it was a "bittersweet" experience.)
Greetings
Covean
|
|
|
|
|
Once back in the early 90s when internet connections were not so common. I was working on some general exception trapping in an application written in C, to test it I added something like this to startup code of the application
int* ptrDummy = NULL;
if (*ptrDummy == 0)
Having finished that I checked it in to the main product code which got build into a special release for a customer who had had some problems with the software. An engineer was sent from our office in the uk to the customer in the South of France with the software on a CD. The engineer then installed it on the customers system, ran it up in frount of the customer only to discover that it crashed on startup. Sorry engineer.
|
|
|
|
|
That's why you should always surround this type of test code with a safety that requires some sort of deliberate configuration change to trigger. I wish I could have learned this before I managed to release an installer with test diagnostic dialogs embedded in it. While our QA team was never able to reproduce the steps needed to trigger things, the customers sure could.
|
|
|
|
|
rentzk wrote: the customers sure could
They are relatives of Murphy[^].
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
hmmm.... Murphy's in-Laws...
|
|
|
|
|
On the Plus side this was easy for me to fix.
|
|
|
|
|
Shouldn't you be telling this to a priest in a confessional booth?
Steve Wellens
|
|
|
|
|
Perhaps you've hit upon an idea for a new message board 'Coding Confession Box'
|
|
|
|
|
In most cases when see code that can catch an access violation your looking at a coding horror.
Steve
|
|
|
|
|
Actual the code for catching the access violations was pretty good, It logged a snap shot of the callstack to a file, then using witchcraft .map and .code files you could find and fix the problem back at base without having to reproduce the problem.
|
|
|
|
|
I had written some code many years ago (VAX Basic I think) and had asked a student programmer to tidy it up and annotate it. He came back a few hours later and said he was finished. I took a look and he had rearranged all the lines of code in alphabetical order ....
|
|
|
|
|
Ouch!
That sounds worse than dropping a old box of punch cards ...
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Look at the bright side.
Now you have an alphabeticaly ordered list of bugs.
I bug
|
|
|
|
|
If they had line numbers, that might not be so bad.
|
|
|
|
|
If I wanted to mess with my teacher that would be the way to do it...but I personally drew ASCII art with my source to mess with them
|
|
|
|
|
Did he sort it by hand or fed the code to some elegant sorting algorithm he invented
|
|
|
|
|
Are you kidding? He probably didn't know the meaning of the word invented and certainly didn't know what an algorithm was.
|
|
|
|
|
'-?Ibbdddeeeehiillnooooopssttuuuvwwy
oops, sorry, I mean:
I don't beleive you - who would be so stupid?
|
|
|
|
|
Impressive. I'm not sure if he'll make a good computer programmer but he sure as hell would make a good computer.
Steve
|
|
|
|
|
He may be the best bugger of the world
Don't forget to Click on [Vote] and [Good Answer] on the posts that helped you.
Regards - Kunal Chowdhury | Software Developer | Chennai | India | My Blog | My Tweets | Silverlight Tutorial
|
|
|
|
|
<br />
";".ToCharArray()<br />
'nuf said.
|
|
|
|
|
Did I mention that this was an argument to string.Split() which is defined to take (params char[])?
They could have simply typed:
';'
|
|
|
|