Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Trigger Best Practices


By:   |   Updated: 2019-06-06   |   Comments (5)   |   Related: More > Triggers

Problem

Using SQL Server triggers can be tricky, so in this tip I will tell you about best practices to adopt when working with SQL Server triggers.

Solution

When writing Transact-SQL code, developers must follow internal coding rules that have been established by the company. Most of the time those rules are focused on how the code is written leaving aside what the code is doing (or trying to do). In other words, the internal coding guidelines of most enterprises covers aspects like naming conventions, capitalization of statements and variables, and tabulations just to mention a few.

But even the nicest and easiest to read piece of code can become a nightmare if you have to find a bug. This is specially the case when triggers are involved.  So, I decided to write this as a reference guide to cover what is usually not covered in a company’s internal coding rules.

SQL Server Trigger Best Practices

We know that a SQL Server trigger is a piece of procedural code, like a stored procedure which is only executed when a given event happens. This implies that triggers cannot be executed manually. This fact makes them very hard to debug. The reason for this is that the debugging difficulties are not limited to the trigger itself, like what happens when debugging a stored procedure. Instead a trigger can affect the function of stored procedures, functions and even ad-hoc queries.

Check out these best practices.

1 - Limit the number of SQL Server triggers per table or view

SQL Server has no limitation on the number of triggers that you can define on a table or view, but as a best practice it’s recommended to limit the number to one trigger or at most two associated with the same firing event on an object.  For example, there is no problem if you have a table (or view) with three triggers and each of those triggers fires separately for INSERT, UPDATE and DELETE statements. Having more triggers per object adds unnecessary complexity at the time of debugging and establishing trigger execution order. If you have many triggers per object, I suggest you to review your business rules. Take some time to move the code of those triggers to stored procedures and then add the call of the stored procedure to one single trigger.

2 - SQL Server Triggers Should Work with Multiple Rows

This is a very common mistake, especially when an inexperienced developer tries to enforce some kind of user defined data integrity constraint.

In order to explain this, let's take as an example the code from the tip SQL Server Referential Integrity Across Databases Using Triggers where I use triggers to enforce referential integrity across databases. If we take a look at the script below you can see that I am checking for the existence of matching rows between the DELETED pseudo table and the HR.dbo.Employees table and doing this as a set instead of row by row.

USE SecDB;
GO
CREATE TRIGGER TR_Users_Employees_Delete ON dbo.Users
    INSTEAD OF DELETE
AS
    SET NOCOUNT ON
    IF EXISTS ( SELECT  0
                FROM    Deleted D
                        INNER JOIN HR.dbo.Employees E ON D.UserID = E.UserID )
        BEGIN
         ;
            THROW 51000, 'You Need to delete the Employee First', 1;  
        END
    ELSE
        BEGIN
            DELETE Users FROM Users U INNER JOIN Deleted D ON D.UserID = U.UserID  
        END
GO

RBAR In a SQL Server Trigger - Worst Practice

In the example code below, I will show you how the same trigger could be written using a RBAR (Row by Agonizing Row) instead of a set-based logic. Where each row is processed one by one and will be much slower.  This is bogus code and a worst practice.

USE SecDB;
GO
CREATE TRIGGER TR_Users_Employees_Delete ON dbo.Users
    INSTEAD OF DELETE
AS
 
DECLARE @UserID INT
 
SELECT @UserID = UserID
FROM deleted  
    SET NOCOUNT ON
    IF EXISTS ( SELECT  0
                FROM    Employees 
                WHERE   UserID = @UserID )
        BEGIN
         ;
            THROW 51000, 'You Need to delete the Employee First', 1;  
        END
    ELSE
        BEGIN
            DELETE FROM Users WHERE UserID = @UserID 
        END
GO

3 - Avoid Complex Logic in SQL Server Triggers

You should try to avoid creating triggers that contain calls to stored procedures or user defined functions. When working with triggers we must aim for fast execution, and including calls to other objects may end up resulting in excessive recompilations and non-efficient plans being cached. You can also suffer from parameter sniffing if you are unlucky enough when executing stored procedures or functions from inside a trigger.

In the case when you cannot avoid calling other objects because your business rules are complex, then make sure you focus on optimizing the code of the stored procedures or user defined functions you are using inside the trigger.

Another thing that you must watch out for is nested triggers. Nested triggers are triggers whose execution fires another trigger, either on the same table or a different table.  A particular case of a nested trigger happens when a trigger fires the execution of another instance of itself, this is known as a recursive trigger.

4 - Maintain Documentation for your SQL Server Triggers

Triggers possess a quality that stored procedures don’t have: They are invisible to the user. In other words, if you are not aware that there is a trigger associated with a given table that your code is using, your code may not work as expected. This can take you a lot of time to figure out, especially if your code is large and complex. Let’s take a look at an example. Suppose you are creating a query that inserts data into a table, let’s call it Customers, and this table has a trigger associated with it. If the trigger on the Customers table is not documented you will for sure spend a lot of time figuring out why the query you are coding keeps failing.

In the next code section, the code for the table and trigger creation is set up in the test environment. As you can see, the INSTEAD OF INSERT trigger replaces the value of the CustomerSince column with the current date (i.e. the value of the GETDATE function).

CREATE TABLE Customers (
CustomerID    INT IDENTITY (1,1) PRIMARY KEY CLUSTERED,
CustomerName  VARCHAR(50),
CustomerSince DATETIME
)
GO
 
CREATE TRIGGER TR_Customers ON dbo.Customers 
INSTEAD OF INSERT
AS
   SET NOCOUNT ON

   INSERT INTO dbo.Customers ( CustomerName ,
                               CustomerSince )
   SELECT CustomerName ,
           GETDATE()
   FROM Inserted;
GO

Now let’s see what happens when we try to insert a row with a specific value for the CustomerSince column.

INSERT INTO dbo.Customers ( CustomerName, CustomerSince )
VALUES ( 'John Doe', '20160101' )
GO
 
SELECT * FROM dbo.Customers
GO

As you can see in the screen capture below, when we try to insert a specific value into the Customers table for the CustomerSince column, the value saved is different than the value passed to the T-SQL statement.  We didn't receive an error message or any warning to make us aware of why the inserted row doesn’t have the value we tried to insert in the CustomerSince column.

The trigger execution is transparent to the user.

5 - Stay on the Same Server when working with SQL Server Triggers

Using remote queries in triggers is a bad practice. Distributed queries have detrimental effects on performance and you should avoid this practice if possible.  In the case you have to implement referential integrity between databases residing on different servers try to keep the usage of remote queries to a minimum.

6 - Perform Validations First for SQL Server Triggers

This is best practice we often forget. Let me be clear, from a logical point of view you can make your validations wherever you want, but from a performance point of view it would be great if we check the conditions at the start of the trigger’s code. For example, you can check if there was a row affected before starting to run any code by using the following lines.

IF (@@ROWCOUNT = 0)  RETURN;

By using the @@ROWCOUNT statement you can check for the rows affected, and if there were no rows affected, return execution to the caller and don't bother executing the rest of the trigger code.

Additionally you can check for changed column values (either inserted or updated) by using the UPDATE (Transact-SQL) or the COLUMNS_UPDATED functions.

Next Steps


Last Updated: 2019-06-06


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, June 11, 2019 - 9:39:04 AM - Sean Lange Back To Top

I think perhaps you are confused about what RBAR means. That is an acronym for "Row By Agonizing Row". This usually indicates some kind of loop. The trigger example is NOT RBAR, it processes only one value. This is a huge logical problem but it is not RBAR.

As for the semicolon, if you are going to claim it is best practice to use it to terminate statements then you should do it all the time. Not just when a statement requires the previous one to be terminated. ;)


Monday, June 10, 2019 - 10:58:54 PM - Daniel Farina Back To Top

Hi Sean,

You are right about the join! Thank you very much for letting me know :-)! We updated the code in both tips.

Regarding the RBAR trigger, as I told to Junaid, the confusion relies on "RBAR in a trigger" vs "RBAR data processing".

The semicolon is the command delimiter in SQL Server. So it is a best practice to use it. But there are cases like the THROW instruction that requires that a semicolon marks the end of the previous command.

Thank you very much for commenting!!!


Monday, June 10, 2019 - 10:29:12 PM - Daniel Farina Back To Top

Hi Junaid,

I intentionally created that error, please let me explain. A RBAR in a trigger is a trigger that works with one single row. On the other hand, using a cursor inside the trigger's code is just another case of RBAR data processing.

Thank you very much for commenting!!!


Friday, June 07, 2019 - 9:54:32 AM - Sean Lange Back To Top

The first piece of code under #2 is a bit odd to me. Why do a LEFT JOIN and then have to check for E.UserID? Just make that an inner join and you don't need a where predicate. It is also a lot easier to understand.

IF EXISTS ( SELECT  0
                FROM    Deleted D
                        LEFT JOIN HR.dbo.Employees E ON D.UserID = E.UserID
                WHERE   E.UserID IS NOT NULL )

And your RBAR example is not actually RBAR. It is a logical error that will only get the last value from deleted (and with no order by it is impossible to be sure which row it really is).

Overall though this is code article with good information. The one thing I would suggest to add is the idea that triggers are widely over used and can almost be avoided with a change to the design.


Thursday, June 06, 2019 - 11:29:29 PM - Junaid Ahmad Back To Top

Example 2.2 is wrong (besides the typo). 

SELECT @UserID = UserID
FROM deleted

Will just give you the last ID from the set ignoring all others. So your rule will only be applied on 1 row in the set.

If you really want to make your trigger slow (and still be correct), try introducing a cursor.


Learn more about SQL Server tools