Solved

How to know a Trigger is active or disable in SQL

Posted on 2011-03-16
9
377 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to install/upgrade the Blitz responder kit 8 42
SQL query with cast 38 43
SQL NULL vs Blank 26 36
SQL Server: Unable to remove duplicate sets in Header/Detail 6 25
'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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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