Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Beware of Logic Errors in your T-SQL Triggers


By:   |   Read Comments (1)   |   Related Tips: More > Triggers

Attend these FREE SQL Server 2017 webcasts >> click to register


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.

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 MSSQLTips.com.



Last Update:


signup button

next tip button



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.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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


Learn more about SQL Server tools