How to know a Trigger is active or disable in SQL

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?
rayluvsAsked:
Who is Participating?
 
rayluvsConnect With a Mentor Author Commented:
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
 
LowfatspreadCommented:
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
 
rayluvsAuthor Commented:
Its telling that "sys.triggers" is not valid
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
LowfatspreadCommented:
which version/edition of sql server are you using?

sys.triggers exists from sql 2005
0
 
rayluvsAuthor Commented:
SQL 2005
0
 
rayluvsAuthor Commented:
Excuse me.... the server is SQL 2000

Is ther way to know if SQL2000?
0
 
LowfatspreadCommented:
have you tried looking at the status column in sysobjects?

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

0
 
rayluvsAuthor Commented:
yes, both stats_schema_ver, status, but no change
0
 
rayluvsAuthor Commented:
I solved the problem and place the solution for future members to make references.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.