• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to check if triggers are enabled in MS SQL Server database?

 
tom chansky
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does anyone know how to use JDBC to programmatically check if triggers are enabled in a SQL Server database?
 
Omar Al Kababji
Ranch Hand
Posts: 357
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tom I am not sure but try executing this query using your favourite SQL query browser

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]


this should give you a list of the triggers in your DB and their status.

if the query is ok then you can use java to play with the result and do your busniss logic. for example if they are all disabled then it means that triggers are disabbled.


hope it helps

(peace)
 
tom chansky
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you for the solution. I think that select query would be very helpful.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If this is MS SQL 2005 or newer the easier approach is to use the sys.triggers view.

This:

will give you a count of all enabled triggers in your schema.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic