CATCH Block in Triggers - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

CATCH Block in Triggers

Author

Topic

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-09-26 : 08:23:38

Hi All,

I have triggers to insert/update/delete table changes to another database table...
[CODE]
USE DB1
GO
CREATE TABLE Trigger_RedirectChanges
ON dbo.Table1
FOR INSERT, UPDAET, DELETE
AS

BEGIN TRY

IF EXITS ( SELECT * FROM inserted) and EXISTS (SELECT * FROM deleted)
BEGIN
UPDATE t1
SET t1.Col2 = i.Col2,
t1.col3 = i.Col3
FROM inserted i JOIN DB2.dbo.Table1 t1 ON t1.ID = i.ID
END
ELSE IF EXITS ( SELECT * FROM inserted)
BEGIN
INSERT DB2.dbo.Table1 SELECT * FROM inserted
END
ELSE
BEGIN
DELETE FROM a WHERE ID IN (SELECT ID FROM deleted)
END
END TRY
BEGIN CATCH
-- Here i need to log any errored record to some other table called 'FailureData'

INSERT INTO FailureData( TableName, PrimaryKeyValue, ErrorMsg, LogTime)
SELECT 'DB1.dbo.Table1', ID, ERROR_MESSAGE(), GETDATE()
END CATCH
[/CODE]

The above code is working fine for 'Happy Flow'; if any error occurs at the time of moving data to second database ( called DB2), it is raising error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." in the CATCH block after inserting failed records and then it is getting rolled back....

Please suggest the solution

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources