Status of trigger : enabled / disabled

rwillemainrwillemain Posts: 8
edited February 11, 2012 3:24PM in SQL Compare Previous Versions
In a comparison of what are identical dbs, I can see triggers as such, but in no apparent way can I determine if there are enabled or disabled.
Bummer ! Missed a big problem in this case ... Any ideas ?
Thanks, Rick

Comments

  • Anu DAnu D Posts: 876 Silver 3
    Thanks for your post.

    Unfortunately SQL Compare doesn't display a difference if the trigger is enabled on one database and not on another.

    Displaying the difference in trigger status is currently on the wishlist for SQL Compare, and will hopefully be added to a future version. The feature tracking code for this is SC-3996.

    As a workaround, you can query the status of all triggers on a database using the following:

    SELECT T.[name] as TableName, TR.[Name] as TriggerName,
    CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled')THEN 'Disabled' ELSE 'Enabled' END Status
    FROM sysobjects T INNER JOIN sysobjects TR on t.[ID] = TR.parent_obj WHERE (T.xtype = 'U' or T.XType = 'V')
    AND (TR.xtype = 'TR') ORDER BY T.[name], TR.[name]

    I hope this is helpful.
    Anuradha Deshpande
    Product Support
    Redgate Software Ltd.
    E-mail: support@red-gate.com
  • +1 on showing the difference in trigger status while comparisons. Could save a lot of time troubleshooting issues.

    Is there a way to use the feature tracking code to see what the status is please?
    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org
  • Thank you very much for the query to derive thsi information.
    It is appreciated and vgery useful.
    Sincerely, Rick
Sign In or Register to comment.