Beware of Logic Errors in your T-SQL Triggers

By:   |   Comments (1)   |   Related: > Triggers


I have just started working with triggers and I have discovered that my T-SQL does not work the way I coded it. Is it possible to have logic errors in T-SQL?


Yes, it is very easy to have logic errors in T-SQL, especially in triggers. Remember that the "L" in T-SQL stands for "Language", and because of this you need to treat T-SQL as you would any other programming language. Not only can you have logic errors, but you can also have runtime and syntax errors. Logic errors can be mitigated with thorough testing of your T-SQL code. Don't just assume that because your query, stored procedure, or function works properly on your first test execution that it will work properly every time.

Let's look at an example of T-SQL with a logic error and how we can eliminate this error. In this example, we want our Update Trigger to set the OrderApprovalDateTime to the current date provided by the getdate() function after the OrderStatus is updated to "Approved". The T-SQL for creating this trigger with the logic error is shown below.

Create the table and the trigger

In the image below, we have populated our table with three records.

Insert three records into the table

To test the trigger, we will execute a T-SQL UPDATE statement to set the OrderStatus value to "Approved" for the first row in the table (pkID = 1). After the T-SQL UPDATE command, we then execute a T-SQL SELECT query to make sure the trigger executed correctly. The output from the UPDATE and SELECT statements are shown below. The OrderApprovalDateTime was set to the current date for the Approved order record.

Update the table to test the trigger

So the trigger looks like it worked correctly, right? Let's test again and set the OrderStatus value to "Approved" for the second row (pkID = 2). In the figure below, we see that the trigger worked correctly for pkID=2, but the trigger also updated the OrderApprovalDateTime for the first row, which is incorrect. We updated too many rows.

Test the trigger on the second row and the logic error is found

Fixing the Trigger Logic

We need to fix our logic in our trigger by adding an INNER JOIN to the INSERTED table so that only the updated rows are affected. The INSERTED table includes a row for each row that was updated in our query, so by joining back to this table we can limit the number of rows the trigger will impact. We also need to add criteria so we only update the OrderApprovalDateTime for records where the OrderApprovalDateTime is NULL or the OrderStatus was changed as shown below.

Because we are testing, we will truncate our test table, drop the trigger, create the trigger, and insert our test records into the table. This will allow us to replicate the conditions in which we found the problem.

Truncate the table, drop the trigger, recreate the trigger and insert rows again

We will repeat the test of updating the first row and it looks correct.

Retest the trigger with the first record

We will repeat the test of updating the second row and it looks correct. Notice how the first row's date time was not updated.

Retest the trigger with the second record

We will conduct one more test. This time we will set the OrderStatus value to "Approved" for the third row and the first row. Our trigger should only update the OrderApprovalDateTime value in the third row, because we already approved the first row and set its OrderApprovalDateTime.

Retest the trigger with the third record
Next Steps

Testing your T-SQL should be thorough and take into consideration several possible scenarios that will help to prevent logic errors and run-time errors. Testing and analysis of a set of records instead of just one will help you to write more robust and correct code.

Also, check out more tips on testing in SQL Server on

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

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

View all my tips

Comments For This Article

Wednesday, October 14, 2015 - 4:20:47 AM - Thomas Franz Back To Top (38886)

You should add an
IF UPDATE(OrderStatus)

just before the UPDATE-Statement itself. UPDATE() is a T-SQL function aviable only in triggers that returns TRUE, if the column is updated.

Your current trigger would execute the UPDATE-statement (with 0 rows affected), even if you change any other column (maybe you realize that you had a wrong time zone setting and have to correct the DateTime column by subtracting 1 hour). The UPDATE() function will prevent this

get free sql tips
agree to terms