How to find the trigger that is changing a particular column. Any suggestions?

[tanner]

Large DB, I have manually checked all of the triggers that I can think of. Is there anyway to search for what trigger is affecting a specific column. SQL SERVER 2005.
THanks

[Jerry Winston]

99% of this kind of information is in the meta data in the sys.* views and tables. If you're in the SQL business it's definitely worth learning your sys meta data.

Try this:

SELECT S.name as [Schema] ,T.name as [Table] ,trg.name as [Trigger], definition FROM sys.triggers trg
INNER JOIN sys.objects T
ON
trg.parent_id = T.object_id
INNER JOIN sys.schemas S
ON
T.schema_id=S.schema_id
INNER JOIN sys.sql_modules SQ
ON
SQ.object_id=trg.object_id
ORDER BY T.name,trg.name

Or the long version:

SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
tr.name AS [Name],
trr.is_instead_of_trigger AS [InsteadOf],
CAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
CAST(ISNULL(ted.object_id,0) AS bit) AS [Delete],
CAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(OBJECTPROPERTYEX(tr.object_id,N'ExecIsAnsiNullsOn') AS bit) AS [AnsiNullsStatus],
CAST(OBJECTPROPERTYEX(tr.object_id,N'ExecIsQuotedIdentOn') AS bit) AS [QuotedIdentifierStatus],
NULL AS [Text],
CAST(
                tr.is_ms_shipped
            AS bit) AS [IsSystemObject],
CASE WHEN ted.is_first = 1 THEN 0 WHEN ted.is_last = 1 THEN 2    ELSE 1 END AS [DeleteOrder],
CASE WHEN tei.is_first = 1 THEN 0 WHEN tei.is_last = 1 THEN 2 ELSE 1 END AS [InsertOrder],
CASE WHEN teu.is_first = 1 THEN 0 WHEN teu.is_last = 1 THEN 2    ELSE 1 END AS [UpdateOrder],
ISNULL(smtr.definition, ssmtr.definition) AS [Definition]
FROM
sys.tables AS tbl
INNER JOIN sys.objects AS tr ON (tr.type in ('TR', 'TA')) AND (tr.parent_object_id=tbl.object_id)
LEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id
INNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=1
LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=3
LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=2
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
WHERE
(tbl.name=N'CurrentProfiles' and SCHEMA_NAME(tbl.schema_id)=N'PharmCAS2008')
ORDER BY
[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC

[tanner]

Thank You very much, that is exactly what I needed.

[NeoPa]

Thanks for responding Tanner.

I'll take that to mean you are happy for the post to be selected as Best Answer ;)