Daniel Calbimonte

Are SQL Server database triggers evil?

January 25, 2017 by

Introduction

There is a lot of talk about how bad triggers are, how you should never use them, etc. I wanted to spend some time reviewing fact vs fiction and do an objective analysis of SQL Server database triggers (both DDL and DML), warts and all. We will review alternatives and compare them with triggers to determine advantages vs disadvantages of each approach.

The following experiments will be conducted:

  1. Triggers vs constraints will compare the performance of both solutions
  2. Historical records/auditing using triggers (trigger vs OUTPUT clause)
  3. SQL Profiler vs triggers
  4. DDL Triggers vs Extended events

Getting started

1. Trigger vs constraints

The first example is the simplest one. We will compare a check constraint with a trigger to verify which one is faster.

We will first create a table named testconstraint that will check if the amount column has values below 1000:

We will also create another table named testrigger, which will be similar to testconstraint, but it will use a trigger instead of a check constraint:

We will also create a trigger for the table to verify that the values inserted are lower than 1000:

To compare the performance, we will set the STATISTICS IO and TIME:

This insert values, will have an execution time of 6 ms:


Figure 1. Execution time of an insert in a table with constraints

Let’s try to insert a value above 1000:

The execution time is 0 ms:


Figure 2. If we try to insert a value outside the check constraint, the time is 0 ms.

Now, let’s test the trigger:

As you can see, the execution time is higher than a constraint:


Figure 3. Trigger execution time in an insert operation

If we try a value higher than 1000, the execution time is slightly higher (1 ms):


Figure 4. Execution time when the value exceeds the threshold specified in the trigger

To compare, we will insert one million rows in the table with constraints:

After inserting a million rows, you can check the execution time:


Figure 5. Execution time in a table with a check constraint after inserting a million rows (2 minutes, 45 seconds)

Then, we will do the same with the table with triggers:

And check the execution time:


Figure 6. Execution time in a table with a trigger after inserting a million rows (4 minutes, 25 seconds)

The following table shows the results after running and truncating both tables 5 times each:

Execution time constraint (minutes:seconds) Execution time trigger (minutes:seconds) Difference in %
2:59 2:55 -2
2:37 3:14 19
2:37 2:45 5
2:45 2:45 0
2:35 2:58 13
Average 2 minutes :44 seconds Average: 2 minutes :55 seconds 7

As you can see, the constraint is faster. In this example constraints are 7% faster in average than triggers.

In general, try to use primary keys and unique constraints to verify uniqueness, Default values to specify default values by default, foreign keys to verify the integrity between two tables and check constraints to check specific values. If none of these options works for your needs, maybe the computed columns can be an alternative. Computed columns are virtual columns not stored in a database based on expressions.

2. Historical record tracking (aka auditing) using triggers (trigger vs OUTPUT clause)

It is a common practice to use triggers to record changes in tables as a form of auditing. I saw some companies that are removing triggers and replacing it with stored procedures. They are using the OUTPUT clause. The output clause allows to capture inserted, deleted from INSERT, UPDATE, DELETE and MERGE operations.

We will create 2 tables. One to test the OUTPUT clause and another to test the trigger:

In addition, we will create 2 historical tables to record the insert changes in the tables created above:

We will create a trigger to insert the inserted value and the date of insertion in the historicproductsalestrigger table:

The trigger named historicinsert inserts data in the table historicproductsalestrigger when an insert occurs in the dbo.sales2017trigger.

Here it is how to store the historical records using the output clause (option 1):

OUTPUT used the table inserted, which is a temporal table that stores the rows inserted.

The second option uses triggers. When we do an insert, the historical records are created automatically by the trigger created before:

Let’s compare the performance. We will run insert one million rows using triggers:

To compare, we will insert one million rows using the OUTPUT Clause:

The following table shows the results after running several times:

Execution time OUTPUT clause (minutes:seconds) Execution time trigger (minutes:seconds) Difference in %
5:56 3:52 34
6:05 3:42 39
5:58 3:56 34
5:46 3:48 34
6:01 3:51 36
Average 5 minutes and 57 secondsAverage 3 minutes and 49 seconds Triggers are 35% faster

As you can see, OUTPUT clause is slower than a trigger in some cases. If you need to created historical records of your table changes, be aware that replacing triggers with OUTPUT clause will not improve the performance. In the table, you can see that triggers are 35% faster than the OUTPUT clause.

With OUTPUT, you have more control on the code, because you can extract the inserted and deleted rows in a specific stored procedure whenever you want. The problem with triggers is that they are executed even if you do not want them to. You can of course disable a trigger, but it is very common to activate triggers by accident.

Triggers can be a good choice if there is an external tool that access and inserts data to your database and you cannot access to code, but you need to add some functionality on insert, delete and update clauses.

3. SQL Profiler vs trigger

With SQL Profiler, you can store in a file or a table some SQL events like insert, update, create, drop and many other SQL Server events. Is SQL Profiler an alternative to triggers to track SQL Server events?

In this example, we will track a table insert and store the result in a table.

Let’s take a look to SQL Profiler:

In the start menu, start the SQL Server Profiler:


Figure 7. Starting SQL Server Profiler

We will create a new template to store the T-SQL Statements completed. Go to File>Trace>New Trace:


Figure 8. Creating a new trace

Specify a name for the new template:


Figure 9. Adding a name to the trace

SQL:StmtCompleted will track the T-SQL Statements completed. Select this event:


Figure 10. Selecting the SQL:StmtCompleted to monitor the T-SQL Statements completed

You can specify filters by pressing the Column Filters button:


Figure 11. Adding filters

In DatabaseName, we will only trace the SQL Statements of the AdventureWorks2016TCP3 database. You can use another database of your preference. Save the trace once that the database name is specified:


Figure 12. Filtering events to only events on the Database AdventureWorks2016CTP3

In the Profiler menu, go to New trace:


Figure 13. Creating a new trace

Select the template just created above:


Figure 14. Using the template created in the new trace

In General tab, check the save to table option:


Figure 15. Saving to a table

Specify your Login and password.

Specify the database, schema and table name where you want to store the trace information. If the table does not exist, it will be created:


Figure 16. The table to store the trace information

Run the trace, and to test in profiler, and in SSMS, go to Adventureworks2016CT3 Database or the database that you selected in the filter and run this statements:

In SQL Server Profiler, you will be able to see the statement run including the application name where the statements run, the CPU time, duration, etc.:


Figure 17. Trace information

The problem with SQL Profiler is that it will be deprecated soon (for the database engine, but not for Analysis Services). According to Microsoft, SQL Profiler will be removed in a later version. Why?

Because it consumes too many resources. SQL Profiler is recommended to run in another Server.

In other words, it is not recommended to replace triggers with SQL Profiler. The best alternative to Profiler is extended events.

4. DDL Triggers vs Extended events

DDL triggers are used to execute an action for events like creating a new database, altering a table, granting permissions.

In this example, we will create a table in the master database to store the execution time, SQL User, Event and query executed. This table will store that information when a database is created using triggers.

The table name will be trigger_event and it will store the information of the new databases created:

The following trigger stores the user, query, execution date and event when a database is created in the trigger_event table created above:

EVENTDATA() is a function complementary to triggers that stores the trigger event information using a XML file.

To test the trigger we will create a database named test9:

If we do a select in the trigger_event table, we notice that all the information was stored after the create database statement:

The values displayed are the following:


Figure 18. The table trigger_event stores the information when the trigger is fired

An alternative to triggers is Extended Events. As we said before, SQL Profiler will be removed in the future and the Extended Events will replace them.

In this example, we will create an extended event to detect if a new database was created.

In the SSMS, go to Management>Session and right click and select new session:


Figure 19. Creating a new session

Specify a session name and check the Start the event session immediately after session created and Watch live data on screen as it is captured option:


Figure 20. Session name and schedule properties

In event library, select the database_created event:


Figure 21. Select the database_created event

On the Session created, right click and select Watch Live Data:


Figure 22. Watching the data

To generate an event, create a database:

For some reason, the event appears after a second event:

You will now be able to see the first event:


Figure 23. The data captured

Extended Events is a great alternative to triggers. It is simple and it does not consume as much resources like SQL Profiler.

Conclusions

We learned the following:

  • Constraints should be used whenever is possible instead of triggers because they are faster, they are easier to maintain and require less code.
  • OUTPUT parameters in SQL stored procedures do not have better performance than the triggers, based on the tests I conducted. Therefore, it is not a good choice to replace triggers for performance reasons.
  • SQL Profiler should not be used to replace triggers, because it is a feature that will be removed soon.
  • Extended events can be a good choice to replace DDL triggers in some scenarios.

There are some DBAs that say that we should never use triggers, but if it does not affect the performance of your code, if they are not used all the time, it is not bad to use it in your code. Make sure to disable for massive bulks and be careful with recursive triggers. Triggers are not evil, but they must be used wisely.

Always remember:

“With great triggers comes great responsibility”.

References

For more information, refer to these links:


Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
General database design

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views