Errorhandling Proc and Trigger

Question

Post reply

Errorhandling Proc and Trigger

Tomas Haglund

Mr or Mrs. 500

Points: 551
More actions
December 15, 2011 at 7:28 am

#99506

Hello!
Having a Proc that populate a table (a) and that table has an AFTER INSERT-Trigger that populates other tables (b and c). I have implemented this errorhandling in the trigger after every INSERT to tables (b and c).
:
SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN END
But how can I recieve some kind of error up to the application that executed the Proc?
Probably I whant to to rollback the insert to table (a) too !
/Tomas

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Using RAISERROR

http://msdn.microsoft.com/en-us/library/ms177497(v=SQL.90).aspx

Tomas Haglund Mr or Mrs. 500 Points: 551 More actions · Answer 2

Tomas Haglund

Mr or Mrs. 500

Points: 551

December 19, 2011 at 12:29 am

#1422968

Thank's!

/Tomas

tfifield SSCrazy Eights Points: 9655 More actions · Answer 3

Tomas,

You could look into TRY/CATCH blocks. I use them all the time. They are especially handy when you want to return a particular message. Something like:

DECLARE @ErrMsg VARCHAR(1000)

BEGIN TRY

INSERT INTO MyTable...

SELECT...

END TRY

BEGIN CATCH

SET @ErrMsg = 'Error Inserting MyTable...' + ERROR_MESSAGE()

RaisError(@ErrMsg, 18, 1)

END CATCH

There's a lot you can do with it.

Todd Fifield

Tomas Haglund Mr or Mrs. 500 Points: 551 More actions · Answer 4

Thank's

I have this, do I need the commit or is the transaction automaticly rolled back if I go in to Catch section?

What do you think about this code?

BEGIN TRY

BEGIN TRANSACTION

UPDATE TblWeb SET Klar = 0 WHERE Filename = @p1

UPDATE TblWebArtikles SET Klar = 0 WHERE Filename = @p1

UPDATE TblWebAccounts SET Klar = 0 WHERE Filename = @p1

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SET @ErrMsg = 'Error Update ResetKlar_sp...' + ERROR_MESSAGE()

RaisError(@ErrMsg, 18, 1)

END CATCH

tfifield SSCrazy Eights Points: 9655 More actions · Answer 5

Tomas,

If you start a transaction then you have to commit it or you will have an open transaction. Normally I put the BEGIN TRAN statement before the BEGIN TRY - mostly because there is usually further processing done if the TRY is successful. It's really up to you how you handle the COMMIT.

The CATCH block will set up the error handling, roll back the transaction and either return or go to some common exit point in the code. If you have any sort of logging results, then the roll back has to be done before inserting/updating the log table or that will get rolled back also.

Todd Fifield