A few weeks back I was tasked with locating a specific database trigger. That seems simple enough, right? Well, in this case no one knew exactly which trigger I was looking for. You could call it a needle in a haystack, but maybe that’s not quite the best analogy. In this case we weren’t exactly sure if a trigger was the source of the issue we were investigating but all signs pointed to it being a very likely possibility. It was one of those scenarios where you aren’t quite sure what you’re looking for but once you see it, you just know. Data in multiple tables was being modified once it was hitting the database and causing some serious reporting issues.
If you’re working within Microsoft SQL Management Studio, going through all your triggers can be a bit of a tedious process, even if you know which tables are being affected. Unlike stored procedures which are all stored in a single list on each database, triggers reside on an individual table so sifting through all of them requires you to select each individual table. Needless to say this can be a bit tedious or just down right annoying.
The best method for retrieving all your database triggers is to pull them from the sysobjects DMV. So instead of sifting through numerous tables I scripted out the following code. I’ve actually modified it a bit for this example to make it more broad to pull back all triggers on the database but if you know which tables are being affected you can easily insert a WHERE clause to narrow down the results.
SELECT sysobjects.name AS TriggerName,
USER_NAME(sysobjects.uid) AS TriggerOwner,
s.name AS table_schema,
OBJECT_NAME(parent_obj) AS TableName,
OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS 'Update',
OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS 'Delete',
OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS 'Insert',
OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS 'After',
OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS 'InsteadOf',
CASE (OBJECTPROPERTY(id, 'ExecIsTriggerDisabled'))
WHEN 0 THEN 'Enabled'
WHEN 1 THEN 'Disabled'
END AS TriggerStatus
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
With a few JOINS you can even grab some additional information that will help you get a good idea of the permissions and ownership of each trigger.
I also wanted to know if each trigger was enabled. Since the data is actually referenced in such a way where 1 = disabled and 0 = enabled, which is counter to what you would normally expect, I simplified the data a bit by setting it to either Enabled or Disabled to avoid any confusion.