question about error handling code in trigger

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 

 

Answer 1

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/

Answer 2

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

Answer 3

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?

Answer 4

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

Answer 5

But the "Example C" (CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
)contains no error handling.

Answer 6

Thank you for your links. It would be even better if they discuss error handling in sql server 2005/2008.

Answer 7

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

Answer 8

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.