September 13, 2004 at 12:51 am
Ok, I have a table that was imported in from Access. The tables primary key is name ID, type INT and is the IDENTITY with a seed of 1 and an increment of 1.
Because the table was originally replicated in Access some of the ID numbers were high. The last entry is 1988206756
I wrote a stored proc that return the @@identity for an insert. problem is that when the ID = 1988206756 the output parameter on the Stored Proc returns 8646.
Anybody have any idea what it is I'm running into here.
September 13, 2004 at 12:54 am
Actually... sorry, I should have searched first... I may have just found my own problem. I have a trigger on this table that archives the record on insert, update and delete. I am getting the @@IDENTITY for that table.
Man this s*cks.
September 13, 2004 at 6:44 am
Just one of the reasons for avoiding triggers unless they're absolutely essential. They hide code away that frequently makes it difficult to identify what look like bugs.
If inserts are only being performed by a front-end app, it may be simpler to write an explicit insert to the archive table from within the app.
I realise there are issues with this (data integrity etc) but people sometimes overestimate their need for this type of archiving/duplication.
September 13, 2004 at 11:05 am
Once again, sorry for the silly questions. I think I've solved my problem well enough. @@Identity didn't work because I do have a trigger running that archives the records.
Unfortunately, the trigger seemed to be the best way to go as the database is being accessed from different clients.
But... IDENT_CURRENT worked out well for me. I realize this could actually be a problem if two people did inserts from the seperate programs however, the number of users for this program will be relatively low (under 10) and it should work out fine.
Thanks for the help.
September 13, 2004 at 4:09 pm
You can use SCOPE_IDENTITY() instead of @@Identity or IDENT_CURRENT. It will return the identity value you just inserted and ignore the result of the trigger.
Brian
September 13, 2004 at 4:23 pm
I will test that and then probably use it. Sounds like the best option. I guess if I had done a little reading I probably would have found that.
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply