Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to View Triggers in SQL Server Management Studio


By:   |   Last Updated: 2019-04-03   |   Comments   |   Related Tips: More > Triggers

Problem

SQL Server has many types of triggers that can be created, but finding them using SQL Server Management Studio (SSMS) may not be easy if you are not sure where to look.  In this tip we look at how to use SSMS to find and manage both DML triggers and DDL triggers.

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.

This are the steps to find table scoped triggers in SSMS.

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.

Contextual menu of table scoped triggers.

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.

These are the steps to find view scoped triggers in SSMS.

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

Contextual menu of view scoped triggers.

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.

These are the steps to find database scoped triggers in SSMS.

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.

Contextual menu of database scoped triggers.

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.

These are the steps to find server scoped triggers in SSMS.

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

Contextual menu of server scoped triggers.
Next Steps


Last Updated: 2019-04-03


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips




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.



    



Learn more about SQL Server tools