March 27, 2003 at 4:56 pm
Hi everyone,
I am relatively new to this forum and I tried very hard (for the past hour) to find an answer to my question, but to no avail. On the subject of handling an error, I was wondering whether a duplicate key violation (error 2627) is one that could be recovered from.
Some background:
I am inserting rows into a log table which has a trigger (after insert) which does some processing on the inserted table and inserts a row into another table which uses a primary key. 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. I saw in the forum that someone had suggested making a unique constraint (as opposed to primary key) and then saying ignore 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) and then replacing the offending row. The problem is that execution never gets to that step.
In case it matters, I am inserting into the first table (the one with the after trigger on it) by running another stored procedure from a Query Analyzer window that does a row by row insert.
I would appreciate any solutions/suggestions that may come to mind.
Regards,
Mazi.
March 28, 2003 at 1:58 am
I would do a check first to see if the row already exists, then do an insert if it doesn't exist or an update if it does. Something like this:
IF EXISTS(SELECT * FROM othertable WHERE pk = @valuefrominserted)
BEGIN
UPDATE othertable ...
END
ELSE
BEGIN
INSERT INTO othertable ...
END
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 28, 2003 at 10:26 am
Thank you for your post. As I mentioned, I am really trying to have the optimistic approach work and then recover from failure. Since I seldom (maybe once out of every 100K entries) encounter primary key violations, I want to minimize lookup overhead. This is only an option, however, if I can recover from the failure. If this is NOT an option, then I will have to revert back to the old way (along the lines that you had suggested).
Thanks again.
Mazi.
March 29, 2003 at 2:35 am
OK. Well, then yes, you can catch the error and take action as necessary. This small example should describe what you need to do:
create table a (b int primary key)
insert into a values (1)
select * from a
declare @a int
insert into a values (1)
set @a = @@error
if @a <> 0 update a set b = 2
select * from a
Run the entire script at once in QA and you'll see the effect.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 31, 2003 at 12:15 pm
Chris,
Thank you for your response. There is something special about how things are handled from a trigger, however. I have indeed used this construct elsewhere, but it seems to fail when it is done from within a trigger. It seems as if any errors encountered from within the trigger automatically terminate the original insertion. Flow of control never gets to the statement after. Now, if this is some type of setup issue, then that may be a different story, but we are running with the default options. If you have the time, you may try to run a trigger off of an insert and then see if you could catch this error when you try to insert from the trigger into another table.
Thanks again,
Mazi.
April 1, 2003 at 6:49 am
You're right Mazi. Interesting. A rollback in a trigger rolls back all changes made in the transaction, even outside the trigger, and stops execution of any other statements following in the batch. Apparently this error automatically rolls back the transaction (implicit or explicit) that is going on.
Some info can be found here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_4qcz.asp
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply