Solution

SQL Server Management Studio is a graphical interface that allows the user to
configure, manage and also edit scripts. Although the GUI is easy to use, we must
recognize that knowing where to find objects is not always that easy and this
is true with triggers because
there are different types of triggers and they are not all in the same place in
SSMS.

Triggers in SQL Server Management Studio

There are two types of triggers that can be created:

DML (Data Manipulation Language) triggers and

DDL (Data Definition Language)
triggers.

The DML triggers are those that fire when a SQL
statement tries to change the data of a given table or view. These can be
created on tables and views.

On the other hand, DDL triggers fire when a SQL statement tries to change
the physical structure of the database (i.e. create, alter or delete database objects).
Additionally, there are DDL triggers that fire when there are changes to server
objects (i.e. create, alter or drop linked servers or databases).

In the next sections I will show you how to access to each type of trigger within
SSMS.

Table Scoped SQL Server DML Triggers

If we need to see the triggers on a specific table, we can use SSMS in the following
way. First expand Databases, then expand the database that
contains the table. Next
expand the Tables folder and find the table you are looking for
then expand the table and expand Triggers to see a list of
triggers for the table as shown below.

Now that we found the trigger, right click on the trigger to see a menu of
things you can do from SSMS. If you click on Script Trigger as
you can see the different scripts you can create from SSMS as shown below.

That context menu gives you the chance to modify, script, view dependencies,
enable or disable and delete the trigger. The modify item opens a new script window
in the SSMS editor with the trigger's source code scripted as an ALTER TRIGGER
statement.

View Scoped SQL Server DML Triggers

Additionally, SSMS can be used to look at triggers that are scoped to views.
Follow the same steps as if you were looking at a table scoped trigger,
but instead of expanding the Table folder expand the Views folder. The
next screen capture shows those steps in order.

Also, if you right click on the trigger you will see a menu
similar to the trigger scoped tables.

SQL Server Database Scoped DDL Triggers

If you want to view these triggers go to the Programmability folder
within the database and look
for a subfolder named Database Triggers as shown below.

You will notice on the next screen capture that if you right click on a database
trigger the context menu is slightly different to the one of table and view
scoped triggers. There isn't a Modify item, but still we have the chance to
script the trigger as DROP and CREATE statements. Also, like on the table and
view scoped triggers, we have the options to view the trigger dependencies, enable
or disable and delete the trigger.

Server Scoped SQL Server DDL Triggers

In case we want to see DDL triggers that affect the entire server
we need to look at the Server Objects folder in the server tree view. You will see a child branch
Triggers. Expand the Triggers folder
to see a list of server scoped DDL triggers.

When we right click on the trigger name, we will see a menu
with the same items as the database scoped triggers.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.