Solved

How to know a Trigger is active or disable in SQL

Posted on 2011-03-16
9
378 Views
Last Modified: 2012-05-11
We have a script that displays all triggers in a Table:

   select B.Name as TableName,A.name as TriggerName
   from sysobjects A,sysobjects B
   where A.xtype='TR'
   AND A.parent_obj = B.id

We need to also include if its active or disable.

Is there a way to do this?
0
Comment
Question by:rayluvs
  • 6
  • 3
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35153558
look on sys.triggers


   select B.Name as TableName,A.name as TriggerName
      ,(select case is_disabled when 1 then 'Disabled' else 'Enabled' end
            as status from sys.triggers as x where x.object_id=b.id) as Status
   from sysobjects A,sysobjects B
   where A.xtype='TR'
   AND A.parent_obj = B.id
0
 

Author Comment

by:rayluvs
ID: 35153639
Its telling that "sys.triggers" is not valid
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35157765
which version/edition of sql server are you using?

sys.triggers exists from sql 2005
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:rayluvs
ID: 35160794
SQL 2005
0
 

Author Comment

by:rayluvs
ID: 35160807
Excuse me.... the server is SQL 2000

Is ther way to know if SQL2000?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35171301
have you tried looking at the status column in sysobjects?

sorry i don't have access to sql 2000 at present ...

0
 

Author Comment

by:rayluvs
ID: 35172429
yes, both stats_schema_ver, status, but no change
0
 

Accepted Solution

by:
rayluvs earned 0 total points
ID: 35172549
I found the solution to display the Trigger Status in both 2000 & 2005:


      SELECT a.name as TriggerName,
            CASE OBJECTPROPERTY(OBJECT_ID(a.name), 'ExecIsTriggerDisabled')
            WHEN 0 THEN 'ENABLED'
            ELSE 'DISABLED'
            END AS Status,
            so2.Name as TableName
      FROM SysObjects a
      JOIN SysObjects so2 ON so2.Id = a.Parent_obj
      WHERE a.type = 'TR'
      ORDER BY 1
         
SELECT a.name as TriggerName, 
	      CASE OBJECTPROPERTY(OBJECT_ID(a.name), 'ExecIsTriggerDisabled') 
	      WHEN 0 THEN 'ENABLED' 
	      ELSE 'DISABLED' 
	      END AS Status, 
	      so2.Name as TableName 
	FROM SysObjects a 
	JOIN SysObjects so2 ON so2.Id = a.Parent_obj 
	WHERE a.type = 'TR'
	ORDER BY 1

Open in new window

0
 

Author Closing Comment

by:rayluvs
ID: 35221351
I solved the problem and place the solution for future members to make references.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question