By: Daniel Farina
Overview
In this chapter we will review the benefits of triggers without casting aside its drawbacks.
What are the Pros and Cons of Triggers?
Triggers provide us with the ability to do things that we are unable to do with stored procedures by themselves. But triggers also have drawbacks that could make them a bad idea for certain scenarios.
Pros of SQL Server Triggers
- Triggers are easy to code.
- Triggers allow you to create basic auditing.
- You can call stored procedures and functions from inside a trigger.
- Triggers are useful when you need to validate inserted or updated data in batches instead of row by row.
- You can use triggers to implement referential integrity across databases. Unfortunately, SQL Server doesn't allow the creation of constraints between objects on different databases, but by using triggers you can simulate the behavior of constraints. I wrote a tip about this matter which you can read here SQL Server Referential Integrity Across Databases Using Triggers.
- Triggers are useful if you need to be sure that certain events always happen when data is inserted, updated or deleted. This is the case when you have to deal with complex default values of columns, or modify the data of other tables.
- You can use external code as a trigger by using CLR triggers. This type of trigger specifies the method of an assembly written in .NET to bind with the trigger.
- Triggers can be nested to up to 32 levels. A trigger is considered to be nested when it performs an action that fires another trigger.
- Triggers allow recursion. Triggers are recursive when a trigger on a table performs an action on the base table that causes another instance of the trigger to fire. This is useful when you have to solve a self-referencing relation (i.e., a constraint to itself).
Cons of SQL Server Triggers
- Triggers are not fired on BULK INSERTS unless you include the FIRE_TRIGGERS option in the bulk insert statement.
- Triggers needs to be properly documented.
- Triggers add overhead to DML statements.
- If there are many nested triggers it could get very hard to debug and troubleshoot, which consumes development time and resources.
- Recursive triggers are even harder to debug than nested triggers.
- If you use triggers to enforce referential integrity you have to be aware that triggers can be disabled by users that have the ALTER permission on the table or view on which the trigger was created. To avoid this, you may have to review user permissions.
- For CLR triggers to work you have to enable the "clr enabled" server option using the sp_configure system stored procedure. Enabling CLR code to run in the database instance has a potential security threat because a malicious user can add CLR assemblies to a database and then take control of the server.
Additional Information
- Take a look at this tip about this matter: SQL Server Triggers Pros and Cons.
- SQL Server Trigger Best Practices
- SQL Server Trigger Example
