1. Known limitations of triggers
Nothing is perfect in real life—including triggers.
Let's recap the advantages of triggers and learn about their most common limitations.
2. Advantages of triggers
Triggers provide numerous advantages.
As we've seen, they can be used successfully for database integrity purposes.
Business rules can be enforced and stored directly in the database when using triggers. This makes it simpler to change or update the applications that are using the database, because the business logic is kept in the database itself.
Triggers give you control over which statements are allowed in a database (a good feature when permission schemes don't offer you enough flexibility).
Triggers can help you implement complex business logic fired by a single event.
And they are also used to audit the database for changes or user activity.
3. Disadvantages of triggers
Triggers also have some disadvantages.
First, they are difficult to view and detect.
It can be hard for an administrator to have a clear overview of the existing triggers in a database and their behavior.
This means triggers are not easy to manage in a centralized manner.
Triggers are also invisible to client applications. When debugging code, triggers are difficult to trace in most situations.
And for some triggers, their complex code can make it hard to follow their logic when troubleshooting.
Triggers can also affect server performance when they are overused or poorly designed.
Before learning some best practices to overcome their limitations, let's see how to get a good overview of all your triggers.
4. Finding server-level triggers
Triggers can be difficult to manage when they are undocumented, when they have complex logic in their design, and because they can be created on many levels (server, database, table).
Luckily, SQL Server offers system views that gather all the information about triggers in one place.
For example, this is the statement you need to run to get all the information about server-level triggers.
5. Finding database and table triggers
A similar view will get you details on both database-level triggers and table triggers.
The type of the trigger (database or table) can be determined from the "parent_class_desc" column.
6. Viewing a trigger definition (option 1)
If you only want to look at a couple of triggers, using the graphical interface of SQL Server Management Studio is a good solution: just right-click on the trigger name and script the trigger definition.
A smarter approach is needed, however, when you want to view the definitions for lots of triggers in the database.
7. Viewing a trigger definition (option 2)
If you turn your attention to SQL system views again, you'll find there are several ways to extract trigger definitions.
SQL system views are like virtual tables in the database, helping you to reach information that cannot be reached otherwise.
This example is based on the "sql_modules" system view.
It extracts the definition of a trigger based on its ID, but you don't actually need to know the ID; you can use the function OBJECT_ID to get a trigger's ID using its name.
This example returns the definition for only one trigger, but the code can be adjusted to output more than one result.
8. Viewing a trigger definition (option 3)
You can also get the definition of a trigger using the OBJECT_DEFINITION function.
You need to pass in the ID of the trigger as the function parameter.
To get the ID, use the OBJECT_ID function first, passing it the trigger name.
9. Viewing a trigger definition (option 4)
The last option you can use is the "sp_helptext" procedure, which uses a parameter called "objname".
You can pass the name of the trigger directly to this parameter to get the trigger definition.
In practice, the use of the "sp_helptext" procedure is not that common, mostly because procedures cannot be used inside of SELECT syntax.
Use of the OBJECT_DEFINITION function or the "sys.sql_modules" view is more widespread. Which one you choose is a matter of personal preference.
10. Triggers best practice
We've seen a couple of helpful hints on how to discover and analyze existing triggers in a database, but the following tips should also always be taken into account:
- Make sure your database design is well-documented, so that it's clear for anybody working with it.
- Keep your trigger design simple—avoid complex logic where possible.
- Avoid overusing triggers when they are not needed.
11. Let's practice!
And now let's practice!