Post reply

Error Traps In Triggers

  • June 4, 2003 at 11:13 am

    #81768

    In VB there is "ON ERROR GOTO" functionality. Is there anything similar that can be used in a trigger?

  • June 4, 2003 at 12:00 pm

    #459798

    I think you can achieve the same functionality using @@ERROR and GOTO...

    assuming your trigger consists of an UPDATE statment and you want to GOTO in case of an error :

    <UPDATE STATEMENT>

    IF @@ERROR <> 0

    BEGIN

    GOTO GotoLabel

    END

    GotoLabel:

    <whatever comes here>

    HTH...

    p.s : Any specific reason to use the "ON ERROR GOTO" structure...???

    Edited by - winash on 06/04/2003 12:01:22 PM

  • June 4, 2003 at 12:27 pm

    #459803

    No special reason to use the ON ERROR GOTO structure except it's what I'm familiar with AND I don't want procedures written by other developers that update the table in question to error because of my trigger -- I want to trap the error

  • June 4, 2003 at 12:32 pm

    #459804

    I tried the @@ERROR as follows:

    <Line that might return an error>

    IF @@ERROR <> 0

    BEGIN

    RETURN

    END

    <More triggger code>

    It didn't work; the trigger still generates an error to the calling routine

  • June 4, 2003 at 12:44 pm

    #459805

    By any chance is there a "working" SQL statement before the @@ERROR is checked -

    @@ERROR resets with every SQL statement...

    so if there was an error in an SQL statement and @@ERROR was not checked immediately after this statement did not execute but rather was checked after another "correct" SQL execution then the value will be reset to 0....

    I hope that makes sense....!!!

    anyway - in the code just inlcude the following :

    Declare @Err_Num Int

    <UPDATE STATEMENT>

    SET @Err_Num = @@ERROR

    Print @Err_Num

    This will help check if @@ERROR is being populated....

  • June 4, 2003 at 1:07 pm

    #459810

    Hi,

    Sorry - I didn't really check out the situation before replying...I used QA to test if @@ERROR and GOTO would work...just ran a small trigger test and it doesn't seem to be working for me either...that could be due to some problem in my coding but I'll have to do some more checking and get back to you...

  • June 4, 2003 at 1:58 pm

    #459815

    much apologies...

    I can't get the @@ERROR and GOTO to work in a trigger...the construct works fine in a stored procedure but just doesn't click in a trigger...

    Hopefully someone else might be able to provide a solution....

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

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