Beware of Logic Errors in your T-SQL 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.

 

Source collected from MSSQLTIPS.COM