Trigger management

1. Trigger management

As you saw in previous lessons, you can end up having lots of triggers in your database. One of the main challenges is the ability to manage all these triggers and have a good overview. You will learn how it's done in this lesson.

2. Getting info from sys.triggers

All the necessary information about existing triggers can be extracted from SQL Server system tables or views. These objects are used to store information about everything that is happening in SQL Server. We will first turn our attention to the "sys.triggers" view and explore the information it can provide us.

3. Getting info from sys.triggers

"sys.triggers" contains 13 columns, but we will only look at the most important ones. The "name" column contains the trigger name (given when the trigger is created). The "object_id" column contains a unique identifier for the trigger object. There are a few double columns, where one column contains an integer code and the other column holds the text explanation. For example, the "parent_class" column contains an integer representing the trigger type. If it's a table trigger, the value will be 1, and for a database trigger the value will be 0. The column "parent_class_desc" gives a textual description of the trigger type. The "parent_id" column will tell you the ID of the object the trigger is attached to.

4. Getting info from sys.triggers

The "create_date" column stores the trigger creation date, while the "modify_date" column tells you when the trigger was last modified. If the trigger is disabled, the "is_disabled" column will have the value 1. Otherwise, it will be 0. Similarly, the value in the "is_instead_of_trigger" column will be 1 for INSTEAD OF and 0 for AFTER triggers.

5. Getting info from sys.server_triggers

The "sys.server_triggers" view holds information about server-level triggers. It has exactly the same structure as "sys.triggers", and the columns hold similar information.

6. Getting info from sys.trigger_events

What about identifying the events that will fire a trigger? This information is stored in "sys.trigger_events".

7. Getting info from sys.trigger_events

Among the most useful columns in this view is "object_id", which identifies the trigger. The columns "type" and "type_desc" will tell you which events will fire a trigger. The columns "event_group_type" and "event_group_type_desc" will point you to any group events that will fire the trigger.

8. Getting info from sys.trigger_events

Trigger group events are special events that are used to fire a trigger. They can contain multiple regular events. The advantage is that you do not need to specify the events individually. For example, the group DDL_TABLE_VIEW_EVENTS contains more than a dozen events related to table and view interactions like CREATE, DROP, and ALTER.

9. Getting info from sys.server_trigger_events

A similar view can be used to extract information about the server-level triggers. The view is called "sys.server_trigger_events".

10. Getting info from sys.trigger_event_types

As mentioned before, you do not need to memorize all the existing events that can be used to fire triggers. The full list is accessible in the "sys.trigger_event_types" view, which shows the event types as numbers and as text. It will also show the parent type when the event is part of a larger group. In this example, CREATE_TABLE, ALTER_TABLE, and DROP_TABLE have the value "10018" in the "parent_type" column. In the last row shown here you can see that this type is actually the DDL_TABLE_EVENTS group.

11. Trigger management in practice

In practice, all of this information has to be packed into a useful form. For example, if you want to see a list of triggers along with their firing events and the objects they're attached to, you need to combine information from multiple views. You can get the trigger name and type from "sys.triggers". If you join the output with "sys.trigger_events" based on the "object_id", you can determine the firing events for each trigger. If you further join the "parent_id" of the trigger with the "object_id" from "sys.objects", you can extract the name and type of the object the trigger is attached to. The second join is chosen to be a LEFT JOIN because database-level triggers do not appear as attached to an object. A LEFT JOIN will still keep the results in the case of non-matching join parameters. If we'd used an INNER JOIN, the results would've been filtered only for matching rows and the database triggers would have been filtered out from the list.

12. Trigger management in practice

This is the query result. There are no values for "AttachedTo" and "ObjectType" for database-level triggers, because they are not attached to any kind of object. They simply exist on the database. This script is just one of many you can use in the real world. An essential thing to remember is that the views seen in this lesson are usually combined to get more detailed results, not used in isolation.

13. Let's practice!

Let's practice trigger management!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.