Trigger Exception Handling
-
Hey there,
I have written some triggers that copy data from one database to another (a simplified version of replication for only a handful of tables).
However, since one of the databases is a production environment I am trying to write error handling into the triggers that will allow the initial insert to proceed even if the insert into the secondary table fails for whatever reason. This prevents users having any issues whatsoever in Production and the error can be logged and investigated without any HelpDesk calls being raised.
Is this even possible? I cannot seem to get it to work. Everytime the insert fails on the secondary table it rolls back the entire transaction including the initial insert. I have tried using savepoints and all sorts but nothing has worked.
Anyone have any ideas?
Cheers,
J
-
Hey,
Check out if the T-SQL code below is what you are looking for or helps you get there:
create procedure dbo.usp_logError(@errcode integer, @errmsg varchar(500))
as
begin
insert into error_log(errcode, errmsg) values(@errcode, @errmsg)
end
create procedure dbo.usp_copyTables
as
begin
-- telling SQL Server to continue with transaction after error occurs
set xact_abort off
declare @db_error as integer
begin tran
insert into table1 select * from prod_table1
set @db_error = @@error
if ( @db_error <> 0 )
exec usp_logError(@db_error, 'copying data from [prod_table1] to [table1]')
insert into table2 select * from prod_table2
set @db_error = @@error
if ( @db_error <> 0 )
exec usp_logError(@db_error, 'copying data from [prod_table2] to [table2]')
commit tran
end
You would need to execute usp_copyTables inside your trigger like this:
exec usp_copyTables
Regards,
JP
-
Hi JP,
Thanks for your reply. However, I had in fact tried this thinking that this would work but alas it did not.
I am attaching an example of what I am doing (exact in every form bar the DB, table and fieldnames).
Ignore the else statement, all this is doing is setting a flag in a table that has a trigger associated which then runs a DTS package to send an email to me.
---
CREATE TRIGGER COPY_DATA_I ON TABLE1
FOR INSERT
AS
SET IDENTITY_INSERT TESTDB.DBO.TABLE1 ON
BEGIN TRAN T1
BEGIN
IF NOT EXISTS(SELECT 1 FROM TESTDB.DBO.TABLE1 WHERE ID = (SELECT ID FROM INSERTED)
BEGIN
INSERT INTO TESTDB.DBO.TABLE1
(ID
, Field1
, Field2)
SELECT ID
, Field1
, Field2
FROM TABLE1
WHERE ID = (SELECT ID FROM INSERTED)
END
ELSE
IF NOT EXISTS(SELECT 1 FROM TESTDB.DBO.COPY_FLAG WHERE SENDMAIL = 1)
BEGIN
UPDATE TESTDB.DBO.COPY_FLAG
SET SENDMAIL = 1
END
--END IF
--END IF
END
IF @@Error <> 0
BEGIN
ROLLBACK TRAN T1
RETURN
END
--END IF
COMMIT TRAN T1
SET IDENTITY_INSERT TESTDB.DBO.TABLE1 OFF
go
---
Cheers.
J
-
You wrote:
[...] I am trying to write error handling into the triggers that will allow the initial insert to proceed even if the insert into the secondary table fails for whatever reason. [...] Is this even possible?I'm afraid that it's not possible. In the context of a trigger (in the code of the trigger itself, or in a procedure called by a trigger), when SQL Server encounters an error, it will not execute the next statement (the way it normally does in a procedure); instead, it will rollback the entire transaction and abort the batch.
For an in-depth discussion about error handling in SQL, read the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html#triggercontext
This means that you cannot handle an error in a trigger, you can only prevent it. For example, if you know that the INSERT could fail because of any constraints (unique, foreign keys or check constraints), you should check them before inserting the data.
Another thing: the way you wrote the trigger, it can only handle single-row inserts. You should always write triggers that handle multi-row operations correctly (see "triggers, multirow" topic in Books Online) (or prevent multirow operations from within the trigger itself). For example, the duplicate checking that you've done, could be written like this to handle multi-row inserts:
INSERT INTO TESTDB.DBO.TABLE1 (ID, Field1, Field2) SELECT ID, Field1, Field2 FROM inserted WHERE ID NOT IN (SELECT ID FROM TESTDB.DBO.TABLE1)
Razvan
-
Thanks for your reply Razvan. I didn't think I was going to have much luck with that one.
Re the multirow inserts, the application that is running over the Prod DB only ever does single row inserts. However, I will still change the code to just select the values from INSERTED instead. Not sure why I didn't think to do this in the first place
Cheers,
J
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply