SQL Server Triggers Pros and Cons

By:   |   Updated: 2019-05-22   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Triggers


Problem

You are working on a new project that uses SQL Server and you are considering using triggers, but you are unsure of the advantaged and disadvantages.  In this tip we will cover the pros and cons of using SQL Server triggers.

Solution

Once you understand how triggers work and know the syntax, triggers are easy to implement. From the eye of the beholder, they look like a special kind of stored procedure that instead of being executed on demand, they are executed automatically on each DML statement execution.

Pros and Cons of SQL Server Triggers

The fact is that thinking of triggers as stored procedures hides a set of benefits and drawbacks. Triggers provide us with the ability to do things that we are unable to do with stored procedures by themselves.

Pros of SQL Server Triggers

  • Triggers are easy to code. The fact that they are coded like stored procedures which makes getting started with triggers easy.
  • Triggers allow you to create basic auditing. By using the deleted table inside a trigger you can build a decent audit solution that inserts the contents of the deleted table data into an audit table which holds the data that is either being removed by a DELETE statement or being changed by an UPDATE statement.
  • 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. Think about it, in a trigger's code you have the inserted and deleted tables that hold a copy of the data that potentially will be stored in the table (the inserted table); and the data that will be removed from the table (the deleted table).
  • 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 recently 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 on SQL Server Triggers

  • When you use BULK INSERT to insert data into a table, triggers are not fired unless you include the FIRE_TRIGGERS option in your bulk insert statement. This is very important to keep in mind, because if you have triggers on a table as part of the business logic, and you make a bulk insert without including the FIRE_TRIGGERS option you will lose data consistency.
  • Triggers are difficult to locate unless you have proper documentation because they are invisible to the client. For instance, sometimes you execute a DML statement without errors or warnings, say an insert, and you don't see it reflected in the table's data. In such case you have to check the table for triggers that may be disallowing you to run the insert you wanted.
  • Triggers add overhead to DML statements. Every time you run a DML statement that has a trigger associated to it, you are actually executing the DML statement and the trigger; but by definition the DML statement won't end until the trigger execution completes. This can create a disaster in production.
  • The problem of using triggers for audit purposes is that when triggers are enabled, they execute always regardless of the circumstances that caused the trigger to fire. For example, if you only need to audit the data inserted by a specific stored procedure and you use a trigger, you may have to delete the rows of the audit table that were created when someone changed data using an ad-hoc query or add more logic into the trigger's code which of course impacts performance. In such case you may have to use the OUTPUT clause.
  • 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. To avoid this, you have to strengthen security and try to avoid using unsafe assemblies.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2019-05-22

Comments For This Article

















get free sql tips
agree to terms