Pros and Cons of Triggers in SQL Server (2024)

By: Daniel Farina

Overview

In this chapter we will review the benefits of triggers without castingaside 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 withstored procedures by themselves. But triggers also have drawbacks that could makethem a bad idea for certain scenarios.

Pros of SQL Server Triggers

• Triggers are easy tocode.
• Triggers allow you tocreate basicauditing.
• You can call storedprocedures and functions from inside a trigger.
• Triggers are usefulwhen you need to validate inserted or updated data in batches instead of rowby row.
• You can use triggersto implement referential integrity across databases. Unfortunately, SQL Serverdoesn't allow the creation of constraints between objects on differentdatabases, but by using triggers you can simulate the behavior of constraints.I wrote a tip about this matter which you can read here SQLServer Referential Integrity Across Databases Using Triggers.
• Triggers are usefulif 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 defaultvalues of columns, or modify the data of other tables.
• You can use externalcode as a trigger by using CLR triggers. This type of trigger specifies themethod of an assembly written in .NET to bind with the trigger.
• Triggers can be nestedto up to 32 levels.A trigger is considered to be nested when it performs an action that fires anothertrigger.
• Triggers allow recursion.Triggers are recursive when a trigger on a table performs an action on the basetable that causes another instance of the trigger to fire. This is useful whenyou have to solve a self-referencing relation (i.e., a constraint to itself).

Cons of SQL Server Triggers

• Triggers are not firedonBULK INSERTS unlessyou include the FIRE_TRIGGERS option in the bulk insert statement.
• Triggers needs to beproperly documented.
• Triggers add overheadto DML statements.
• If there are many nestedtriggers it could get very hard to debug and troubleshoot, which consumes developmenttime and resources.
• Recursive triggers areeven harder to debug than nested triggers.
• If you use triggersto enforce referential integrity you have to be aware that triggers can be disabledby users that have the ALTER permission on the table or view on which the triggerwas created. To avoid this, you may have to review user permissions.
• For CLR triggers towork you have to enable the "clr enabled" server option using thesp_configure system stored procedure. Enabling CLR code to run in the databaseinstance has a potential security threat because a malicious user can add CLRassemblies 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