Solved

How to know a Trigger is active or disable in SQL

Posted on 2011-03-16
9
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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