Solved

How to know a Trigger is active or disable in SQL

Posted on 2011-03-16
9
369 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
 

Author Comment

by:rayluvs
ID: 35160794
SQL 2005
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now