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.
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.
Next Steps
- This tip was written using SQL Server Management Studio v17.9. If you still using an older version of SSMS I suggest you read the following tip to see if it’s worth upgrading New Features in SQL Server Management Studio v17. Additionally take a look at this tip SQL Server Management Studio 17.x Important Features.
- If you don’t have SSMS installed take a look at this tip for a quick guide on How to Install SQL Server Management Studio on your Local Computer.
- If you know the trigger name you can use the object search feature of SSMS. You can learn more about this here Using Object Explorer Details and Object Search Feature of SSMS 2008.
- If you don’t know the trigger’s name you can use the scripts from this tip: Find All SQL Server Triggers to Quickly Enable or Disable.
- If you need to script triggers for any database you can take a look at the following tip Script triggers from any database in SQL Server.
- Stay tuned to the SQL Server Triggers tips category for more tips and tricks using triggers.
- For more tips related to SSMS you can browse the SQL Server Management Studio tips category.
