Overview
In this chapter, we will review the benefits of triggers without casting aside their drawbacks.
Explanation
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 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 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 need 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 who 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
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning. He studied at Universidad de Buenos Aires. Daniel started working as a programmer at a young age. Over the years he specialized in databases, particularly SQL Server and Oracle. Now with 30 years of age, his work experience includes working with various technologies like VB, C, .NET, web development, Windows and Linux systems. He likes to read about science, psychology, philosophy and many other things. In his spare time, he trains powerlifting aiming to compete.
- MSSQLTips Awards: Author of the Year – 2018 | Champion (100+ tips) – 2018 | Author Contender – 2015-2017, 2019
