Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • 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.
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!