Triggers are the types of objects in SQL Server that are easy to lose track of. There isn’t an obvious way to tell that a table has a trigger on it and since most tables don’t have triggers, this is one of the things people often miss when troubleshooting unexpected results.
DDL triggers are worse, since they aren’t tied to particular tables, but rather events. How can you find DDL triggers in your environment?
There are a few ways. I’ll show you visually and in code.
The GUI
I like the Management Studio GUI to find information, and to quickly get code written. With SQL Prompt installed, I can get great intellisense that makes it easy to find parameters, names, objects, etc. I don’t like to run the actions from SSMS, but rather use the Script button and save the code, and execute it in a query window.
In looking for server-side triggers, there is a “Server Objects” folder in the tree.
Here is where you find your backup devices, endpoints, linked servers, and server level triggers. In this case, I can expand the folder (shown above) and find the trigger I created recently.
At the database level, there’s a similar structure. Inside of a database, we find there is a programmability folder, which contains all the code items I can create in a database.
In here we can see there is a Database Triggers item, and inside there are two triggers that I setup inside this database.
You have to go look for these triggers, but if you’re wondering if they exist, you can find them here.
Code
The best way to look for triggers quickly is with code. Without resorting to BOL, I suspected there was some DMV that contained trigger code. As you can see below, I was right as typing SSF (a shortcut in Prompt), followed by “master.sys.server_t” got me this result:
If I then examine the results from the server_triggers table, I get my one trigger at the server level.
This is only part of the information needed as the server_trigger_events table has the events that will fire this trigger. I can query that to see I only have one event here:
If I join in the events, then I can clean this up and get this:
select
t.name
, t.object_id
, t.is_disabled
, te.type_desc
FROM master.sys.server_triggers t
INNER JOIN master.sys.server_trigger_events te
ON t.object_id = te.object_id
Which shows me the trigger, its ID, and the event’s.
