locked
question about error handling code in trigger RRS feed

  • Question

  • The following trigger has no error handling code, neither using "try catch" nor "if(@@error<>0).


    ALTER TRIGGER [dbo].[SomeTrigger]
       ON  [dbo].[VoyageOperation]
       AFTER INSERT
    AS
    BEGIN
     IF @@ROWCOUNT = 0 RETURN

     SET NOCOUNT ON;


      UPDATE dbo.Voyage
      SET IsClose = 1
      WHERE VoyageId IN (SELECT VoyageId
           FROM INSERTED
           WHERE VoyageOperationTypeId IN (3,4))

    END

     

    when "insert into VoyageOperation values(....)" is executed, if the trigger's update raise an error, the insert statement still success, that is not what i want, and it is a big problem.

    To avoid the problem above,  we must use the following template with error handling to write every trigger so that your trigger has a chance to rollback transaction when error raises within the trigger. I am not 100% sure if my opinion is right. Looking forward to your opinions. Thank you.

    CREATE TRIGGER xxxTrigger
    ON SomeTable
    AFTER INSERT
    AS
    BEGIN

       BEGIN TRY
           --[modification blocks][modification section]
       END TRY
       BEGIN CATCH
                  IF @@trancount > 0
                      ROLLBACK TRANSACTION

                  DECLARE @ERROR_MESSAGE nvarchar(4000)
                  SET @ERROR_MESSAGE = ERROR_MESSAGE()
                  RAISERROR (@ERROR_MESSAGE,16,1)

         END CATCH
    END 

     

    Monday, June 28, 2010 11:18 AM

Answers

  • Yes, you do need error checking in every trigger.  You should have error checking in virtually every piece of production code you write.  Some errors force an immediate rollback and if you get one of those errors in a trigger, the insert that fired the trigger will be rolled back, but there are many errors that do not force an automatic rollback.  And if you get one of the errors that does not automatically force a rollback (like, for example, a primary key violation), then if you do not have error checking in your trigger, the trigger will not do a rollback and the original insert (or update or delete or merge) that fired the trigger will not fail.

    It is true that Microsoft's code in BOL usually does not have error checking code.  I presume they do this so that their examples are simple and straight-forward.  But you definitely want it in your production code.

    Tom

    Wednesday, June 30, 2010 3:46 AM
  • But the "Example C" (CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
    )contains no error handling.

    It do not have the Try and catch block , But it has an rollback mechanism with throwing an error. This is wat exactly your scenario too... You can apply this for error handling too.
    Wednesday, June 30, 2010 5:24 AM

All replies

  • I would not use trigger at all in that case, why not having an UPDATE withing a storedf procedure that does INSERT and check for  VoyageOperationTypeId IN (3,4)) there????

    BTW what version are  you using?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, June 28, 2010 11:27 AM
    Answerer
  • Yes , what ever you have designed looks good.

    Refer to the "Example C" in the below link

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

    Monday, June 28, 2010 11:42 AM
  • thank you for your reply.

    I use trigger to protect data integrity. The "IsClose" column in the Voyage table is the result of denormalization. When VoyageOperation row with VoyageOperationTypeId 3 or 4 is inserted into the VoyageOperation table, the voyage is considered "close". Since my application code is not the only code accessing the database, data importing tools such as SSIS or raw queries executed by DBA to fix data problems are out of my application's control. Using trigger insure that no mattter where the query comes from, my database's data integrity is protected.

    I am using sql server 2008.

    So back to my question, is the template above a best practice?

    Monday, June 28, 2010 12:29 PM
  • can you please go through below urls -

    1. http://www.sommarskog.se/error-handling-I.html

    2. http://www.sommarskog.se/error-handling-II.html

    Thanks,

     


    Kapil Khalas
    Monday, June 28, 2010 12:50 PM
  • But the "Example C" (CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
    )contains no error handling.
    Wednesday, June 30, 2010 3:27 AM
  • Thank you for your links. It would be even better if they discuss error handling in sql server 2005/2008.

    Wednesday, June 30, 2010 3:38 AM
  • Yes, you do need error checking in every trigger.  You should have error checking in virtually every piece of production code you write.  Some errors force an immediate rollback and if you get one of those errors in a trigger, the insert that fired the trigger will be rolled back, but there are many errors that do not force an automatic rollback.  And if you get one of the errors that does not automatically force a rollback (like, for example, a primary key violation), then if you do not have error checking in your trigger, the trigger will not do a rollback and the original insert (or update or delete or merge) that fired the trigger will not fail.

    It is true that Microsoft's code in BOL usually does not have error checking code.  I presume they do this so that their examples are simple and straight-forward.  But you definitely want it in your production code.

    Tom

    Wednesday, June 30, 2010 3:46 AM
  • But the "Example C" (CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
    )contains no error handling.

    It do not have the Try and catch block , But it has an rollback mechanism with throwing an error. This is wat exactly your scenario too... You can apply this for error handling too.
    Wednesday, June 30, 2010 5:24 AM