Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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]
@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

@NJohnson9402 NJohnson9402 commented Dec 2, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.