Post reply

Catching error from trigger in a stored procedure

  • December 9, 2003 at 1:42 pm

    #83588

    Does anyone know how to catch an error from a trigger when it's rolling back to a stored procedure? Right now we have a stored procedure that keeps looping through because we can't seem to catch the error from the trigger. Can this be done? Need help. Stored procedure is too long to post to the forum. Any help would be greatly appreciated. TIA,

    Melanie

  • December 9, 2003 at 1:57 pm

    #485425

    can you specify what kind of error are you trying to catch?

    I can't wait for yukon structured exception handling


    * Noel

  • December 10, 2003 at 7:33 am

    #485541

    Here's a good article from Microsoft about error handling within stored procedures: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro2k/html/sql00f15.asp

  • December 10, 2003 at 8:47 am

    #485559

    As a test, I would try a RAISERROR('trigger', 16, 1) in the trigger and catch the error after the query directly invoking the trigger. Perhaps your error is not fatal enough.

    Edited by - rstone on 12/10/2003 08:47:38 AM

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • December 10, 2003 at 2:46 pm

    #485613

    The easiest way is to put a few print statements into the trigger and then execute the stored prcedure in SQL Query Analyzer.

    If you really know how far it is going, you can put in a print after each statement displaying out @@ERROR or @@FETCH_STATUS

    If it is a production database make sure that you code a begin transaction and rollback transaction as not to corrupt the data durring testing.

    Have fun

  • December 10, 2003 at 3:00 pm

    #485616

    If in a production database and the users are complaining about a trigger problem which I can't replicate, I sometimes temporarily insert statements to the effect of:

    RAISERROR('blah blah...', 0, 1) WITH LOG

    ...at various places in the trigger code, to send some tracking information to the SQL error log.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • December 10, 2003 at 3:09 pm

    #485618

    if the idea is to put information into the log only you may want to use xp_loginfo


    * Noel

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

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