locked
Trigger with error handling RRS feed

  • Question

  • Hello,

    I need some guidance in setting up a table trigger with effective error handling.

    I have a staging table which is being populated with data constantly.  (The data is coming from a message queue).  And I need to insert this data into various other places, and then delete it from the stage table.  If there is an error with the insertion, I would like to move that error-inducing record into an error table.

    My first attempt, I wrote a FOR INSERT trigger, that contained a try...catch.  My thinking was that if the try block failed, the catch block could move the error record into my error table.  But this doesn't seem to work, I get a 0 records inserted.  And the record that came in seems to have disappeared.  It's as if the record was never commited to the staging table.

    I then thought about a using an AFTER INSERT, but how do I ensure that the trigger does not pick up the a record that is in "mid-process"?  A temporary table?

    Any guidance much appreciated.

     

     

    Friday, March 19, 2010 5:25 PM

Answers

  • When a trigger rollsback a transaction the entire transaction is rolled back, including the initial insert.  You have to trick SQL Server by using an unpersisted table to hold onto the data and load the error table.  The only type of table that can do this is a table variable.

    The sample below demonstrates the problem and how to code the error handling properly.

     

    USE [tempdb]
    go
    
    CREATE TABLE Test_Trig(
    id TINYINT
    );
    GO
    
    CREATE TABLE Test_Trig2(
    id TINYINT
    );
    GO
    
    CREATE TABLE Trig_Error(
    id INT, col CHAR(3)
    );
    GO
    
    --This trigger fails to insert into the error table
    CREATE TRIGGER tr_Ins_Test_Trig ON dbo.Test_Trig
    AFTER INSERT
    AS
    BEGIN
    	BEGIN TRY
    		BEGIN TRANSACTION
    	
    		INSERT INTO Test_Trig2
    		SELECT id + 1 FROM INSERTED
    		COMMIT TRANSACTION 
    	END TRY
    	
    	BEGIN CATCH
    	
    	ROLLBACK TRANSACTION
    	INSERT INTO Trig_Error
    	SELECT *,'err' FROM inserted
    	
    	END CATCH
    END
    GO 
    
    INSERT INTO Test_Trig VALUES (255);
    SELECT * FROM Test_Trig
    SELECT * FROM Test_Trig2
    SELECT * FROM Trig_Error
    
    TRUNCATE TABLE Test_Trig
    TRUNCATE TABLE Test_Trig2
    
    --This trigger works because the table variable is not susceptible to rollback.
    ALTER TRIGGER tr_Ins_Test_Trig ON dbo.Test_Trig
    AFTER INSERT
    AS
    BEGIN
    	BEGIN TRY
    		BEGIN TRANSACTION
    	
    		INSERT INTO Test_Trig2
    		SELECT id + 1 FROM INSERTED
    		COMMIT TRANSACTION 
    	END TRY
    	
    	BEGIN CATCH
    	
    	DECLARE @Trig_Error TABLE(
    	id INT, col CHAR(3)
    	);
    	
    	INSERT INTO @Trig_Error
    	SELECT *,'err' FROM inserted
    	
    	ROLLBACK TRANSACTION
    	
    	INSERT INTO Trig_Error
    	SELECT * FROM @Trig_Error
    	
    	END CATCH
    END
    GO 
    

    http://jahaines.blogspot.com/
    • Marked as answer by WHL999 Sunday, March 21, 2010 3:23 PM
    Friday, March 19, 2010 6:31 PM

All replies

  • FOR and AFTER in the trigger are the same, where FOR, I believe, is the old syntax. So, you can only have INSTEAD OF and AFTER triggers in SQL Server.

    Are you sure you must implement your logic as a trigger? Is it possible to keep it inside the procedure that inserts records into the table?


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, March 19, 2010 5:36 PM
  • When a trigger rollsback a transaction the entire transaction is rolled back, including the initial insert.  You have to trick SQL Server by using an unpersisted table to hold onto the data and load the error table.  The only type of table that can do this is a table variable.

    The sample below demonstrates the problem and how to code the error handling properly.

     

    USE [tempdb]
    go
    
    CREATE TABLE Test_Trig(
    id TINYINT
    );
    GO
    
    CREATE TABLE Test_Trig2(
    id TINYINT
    );
    GO
    
    CREATE TABLE Trig_Error(
    id INT, col CHAR(3)
    );
    GO
    
    --This trigger fails to insert into the error table
    CREATE TRIGGER tr_Ins_Test_Trig ON dbo.Test_Trig
    AFTER INSERT
    AS
    BEGIN
    	BEGIN TRY
    		BEGIN TRANSACTION
    	
    		INSERT INTO Test_Trig2
    		SELECT id + 1 FROM INSERTED
    		COMMIT TRANSACTION 
    	END TRY
    	
    	BEGIN CATCH
    	
    	ROLLBACK TRANSACTION
    	INSERT INTO Trig_Error
    	SELECT *,'err' FROM inserted
    	
    	END CATCH
    END
    GO 
    
    INSERT INTO Test_Trig VALUES (255);
    SELECT * FROM Test_Trig
    SELECT * FROM Test_Trig2
    SELECT * FROM Trig_Error
    
    TRUNCATE TABLE Test_Trig
    TRUNCATE TABLE Test_Trig2
    
    --This trigger works because the table variable is not susceptible to rollback.
    ALTER TRIGGER tr_Ins_Test_Trig ON dbo.Test_Trig
    AFTER INSERT
    AS
    BEGIN
    	BEGIN TRY
    		BEGIN TRANSACTION
    	
    		INSERT INTO Test_Trig2
    		SELECT id + 1 FROM INSERTED
    		COMMIT TRANSACTION 
    	END TRY
    	
    	BEGIN CATCH
    	
    	DECLARE @Trig_Error TABLE(
    	id INT, col CHAR(3)
    	);
    	
    	INSERT INTO @Trig_Error
    	SELECT *,'err' FROM inserted
    	
    	ROLLBACK TRANSACTION
    	
    	INSERT INTO Trig_Error
    	SELECT * FROM @Trig_Error
    	
    	END CATCH
    END
    GO 
    

    http://jahaines.blogspot.com/
    • Marked as answer by WHL999 Sunday, March 21, 2010 3:23 PM
    Friday, March 19, 2010 6:31 PM
  • Use SET XACT_ABORT OFF .When Transact-SQL statement encounter error ,it just raised the error message and the transaction continues processing. 
    The following code is to create the trigger:
       Create TRIGGER [dbo].tr_Ins_Table_Master ON [dbo].Table_Master
         AFTER INSERT
        AS
        BEGIN
    	set xact_abort off
    	BEGIN TRY
                --your SQL		    
    			INSERT INTO Table_Detail
    			SELECT MasterID,Name FROM INSERTED
    
    	END TRY
    	
    	BEGIN CATCH		
    		select ERROR_MESSAGE()
    	END CATCH
    
        END


    Myblog: http://trufflepenne.blogspot.com/ 松露筆管麵

    Thursday, April 20, 2017 5:34 PM