http://www.sqlservercentral.com/blogs/steve_jones/2012/09/13/finding-ddl-triggers/

Printed 2014/08/22 11:53AM

Finding DDL Triggers

By Steve Jones, 2012/09/13

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.

ddl3

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.

ddl4

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:

ddl5

If I then examine the results from the server_triggers table, I get my one trigger at the server level.

ddl6

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:

ddl7

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.

ddl8


Filed under: Blog Tagged: auditing, sql server, syndicated, T-SQL
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.