Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-01 : 07:29:49
|
| mazi writes "Hi there, On the subject of handling an error, I was wondering whether a duplicate key violation (error 2627) is one that could be recovered from. The manual states that severity levels 11-16 are user generated and can be fixed by the user. In this case, the query analyzer tells me that the severity level is 14, yet I cannot process the error - it seems like a fatal error. The following is the error returned as seen in the Query Analyzer:Server: Msg 2627, Level 14, State 1, Procedure rawlog_isert, Line 458Violation of PRIMARY KEY constraint 'costed_pk'. Cannot insert duplicate key in object 'costed'.The statement has been terminated.Some background:I am inserting rows into a log table (rawlog) which has a trigger (rawlog_insert after insert) which does some processing on the inserted row and inserts a row into another table. This second table enforces a primary key constraint. Since I will seldom see a re-insertion of a row that would violate the primary key constraint, and since I needed to speed up my application as much as possible, I decided to try an optimistic approach and do an insert and then recover if there was an error. The problem is that execution never gets to that step.Someone had suggested making a unique constraint (as opposed to primary key) and then ignoring dupes. The problem is that I need to in fact replace the row if one with the same primary key exists. I was doing this by checking for an error (in those rare circumstances one in around 100K) and then replacing the offending row. In case it matters, I am testing this process by inserting into the rawlog table (the one with the after trigger on it) by running another stored procedure from a Query Analyzer window. This stored procedure inserts rows one by one from a test table into the rawlog table - I have purposely put data in here to ensure that there is a key violation.I would appreciate any solutions/suggestions that may come to mind.Regards,Mazi." |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2003-04-01 : 07:58:33
|
| MaziHave a look at "INSTEAD OF Triggers" in BOL i think thats what you wantHTHAndy |
 |
|
|
|
|
|
|
|
Handling Duplicate Key Errors