By: Dallas Snider | Updated: 2015-10-14 | Comments (1) | Related: > Triggers
Problem
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?
Solution
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.
In the image below, we have populated our table with three records.
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.
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.
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.
We will repeat the test of updating the first row and it looks correct.
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.
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.
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 MSSQLTips.com.
- Testing Tips
- SQL Server Unit Testing with Visual Studio 2010
- SQL Query Stress Tool
- Testing SQL Server Query Performance Using Different Levels of Parallelism
About the author
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
Article Last Updated: 2015-10-14
