Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: How to force immediate recompile of triggers and detect errors?

    I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.

    I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)

    I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.

    I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.

    One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Shouldn't this be part of your QA process, not part of your application?

    -PatP

  3. #3
    Join Date
    Mar 2005
    Posts
    16
    No, it's a database configuration application, and there may be triggers that we don't own and didn't create that we have to disable if they're going to cause a problem in our db config process. If we disable any triggers, we'd report the situation and the customer would be expected to fix the triggers before going live again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •