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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • tanner
    New Member
    • Jul 2010
    • 2

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

    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
    Recognized Expert New Member
    • Jun 2008
    • 145

    #2
    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:
    Code:
    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:
    Code:
    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

    Comment

    • tanner
      New Member
      • Jul 2010
      • 2

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

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #4
        Thanks for responding Tanner.

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

        Comment

        Working...