See corresponding blog post at http://natethedba.wordpress.com/finding-trigger-dependencies
/* NJohnson9402 / natethedba.wordpress.com | |
Uses system catalog views and DMVs to find trigger table and column dependencies; | |
tailored as "audit trail" example, but applicable to most trigger situations. | |
Triggers always depend on & are a child object of the table on which they exist. | |
In this example, the predicate "OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1" | |
restricts the view only to "ON/AFTER UPDATE" triggers; but you could easily extrapolate | |
to include the other types (INSERT, DELETE) as well, using the same model; | |
just replace 'ExecIsUpdateTrigger' with 'ExecIsInsertTrigger' etc. | |
*/ | |
SELECT [Trigger] = trig.name | |
, [On Table] = tab.name | |
, [Key Column(s)] = col.name | |
, [Audited Columns] = '' | |
, [Key Pos] = icol.key_ordinal | |
FROM sys.triggers trig | |
JOIN sys.tables tab | |
ON trig.parent_id = tab.object_id | |
JOIN sys.schemas sch | |
ON tab.schema_id = sch.schema_id | |
JOIN sys.key_constraints kc | |
ON tab.object_id = kc.parent_object_id | |
AND tab.schema_id = kc.schema_id | |
JOIN sys.index_columns icol | |
ON tab.object_id = icol.object_id | |
AND kc.unique_index_id = icol.index_id | |
JOIN sys.columns col | |
ON icol.column_id = col.column_id | |
AND icol.object_id = col.object_id | |
WHERE OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1 | |
AND trig.name LIKE 'AuditTrail%' | |
--AND tab.name = 'Search-for-a-Table' | |
UNION ALL | |
SELECT [Trigger] = trig.name | |
, [On Table] = tab.name | |
, [Key Column(s)] = '' | |
, [Audited Columns] = dep.referenced_minor_name | |
, [Key Pos] = 999 | |
FROM sys.triggers trig | |
JOIN sys.tables tab | |
ON trig.parent_id = tab.object_id | |
JOIN sys.schemas sch | |
ON tab.schema_id = sch.schema_id | |
CROSS APPLY sys.dm_sql_referenced_entities(sch.name + '.' + trig.name, 'OBJECT') dep | |
WHERE dep.referenced_entity_name NOT IN ('My_Audit_Trail_Table') | |
AND OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1 | |
AND trig.name LIKE 'AuditTrail%' | |
--AND dep.referenced_minor_name = 'Search-For-An-Audited-Column' | |
--AND tab.name = 'Search-for-a-Table' | |
ORDER BY tab.name, trig.name, [Key Pos], [Audited Columns] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Other object-property interrogators include "OBJECTPROPERTY(trig.object_id, 'ExecIsInsertTrigger')", "OBJECTPROPERTY(trig.object_id, 'ExecIsDeleteTrigger')", and more exotic ones like "OBJECTPROPERTY(trig.object_id, 'ExecIsTriggerDisabled')", "OBJECTPROPERTY(trig.object_id, 'ExecIsAfterTrigger')"; and you can even tell whether it's the first or last enforced-order trigger when there are multiple. You can really get crazy with triggers; I don't recommend it.