Determine if a table is affected by any trigger

Question

Is there a way to determine if a trigger on any given table affects one other specific table?

My actual problems is rows being deleted from a table, and I am confident it happens from a trigger on another table. I need to find this "other table" (there are several hundred tabels).

Thx in advance!

Regards /Snedker

Sorry - it was MSSQL 2008 R2, so you'r other posting went well for me! Regards /Snedker — Morten Snedker, Sep 21 '11 at 11:36

score 4 · Accepted Answer · edited May 23 '17 at 12:06

You can use

SELECT def,
        t.*
FROM sys.triggers t
CROSS APPLY (SELECT OBJECT_DEFINITION(object_id)) C(def)
WHERE def LIKE '%DELETE%' AND def LIKE '%your_table%'

If that doesn't find anything and you want to broaden the search you can use

SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%DELETE%' AND definition LIKE '%your_table%'

If that still doesn't work and you think that the query might be being sent by an application you can use extended events to get to the bottom of things as per my answer here.

Determine if a table is affected by any trigger

1 Answers1