SQL Server Performance Forum – Threads Archive

Where are triggers stored?

I see where my stored procedures are stored.
They have their own folder for those objects. There is not a corresponding folder for triggers.
How can I see what triggers are defined on the server? Thanks.

Triggers are associated with a table so if you want to use EM you need to go into design view for the table in question and select the Trigger Icon. A screen will pop up listing all the triggers as a drop down. If you want to use QA to list triggers here is some code (works with SQL Server 2000):
SELECT LEFT(sop.name,36) AS ‘Table’, LEFT(so.name,36) AS ‘Trigger’,
CASE WHEN OBJECTPROPERTY(so.id, ‘ExecIsTriggerDisabled’) = 1
THEN ‘Disabled’ ELSE ‘Enabled’ END AS ‘Trigger Status’
FROM sysobjects so
INNER JOIN sysobjects sop ON so.parent_obj = sop.id
WHERE so.xtype = ‘TR’
ORDER BY 1, 2

Triggers are in sysobjects.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks to both of you for the great info.

from sysobjects you can query
select name from sysobjects where xtype=’tr’ Madhivanan Failing to plan is Planning to fail

All code, no matter what object, is stored in syscomments. Enterprise Manager only graphically separates different objects into "different folders". You can also find this information by help of sp_helptrigger. If you want to run this against the whole db, you either need to loop through it or use the undocumented sp_msforeachtable like this sp_msforeachtable ‘exec sp_helptrigger "?"’
As always the litany, the use of undocumented features should carefully be considered… —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

]]>