Designing a Microsoft T-SQL Trigger
On occasions when building a project involving an Access front-end and a SQL Server backend, we’ve run into this question. Should we use a trigger for something? Designing a SQL Server trigger for Access application may be a solution but only after careful considerations. Sometime this gets suggested as a way to keep the business logic within database, rather than the application. Normally, I like having the business logic defined as close to the database as possible. So, is trigger the solution we want for our Access front-end?
I’ve found that coding a SQL trigger requires additional considerations and if we are not careful we can end up with a bigger mess than we started. The article aims to cover all the pitfalls and techniques we can use to ensure that when we build a database with triggers, they will work for our benefit, rather than just adding complexity for sake of complexity.
Let’s consider the rules…
Rule #1: Don’t use a trigger!
Seriously. If you’re reaching for the trigger first thing in the morning, then you are going to regret it by night. The biggest problem with triggers in general is that they can effectively obfuscate your business logic and interfere with processes that should not need a trigger. I’ve seen some suggestions to turn off triggers when you are doing a bulk load or something similar. I assert that this is a big code smell. You shouldn’t be using a trigger if it has to be conditionally switched on or off.
As default, we should be writing stored procedures or views first. For most scenarios, they will do the job just fine. Let’s not add magic here.
So why the article on trigger then?
Because triggers do have their uses. We need to recognize when we should use triggers. We also need to write them in a way that it helps us more than hurting us.
Rule #2: Do I really need a trigger?
In theory, triggers sounds nice. They provide us with an event-based model to manage changes as soon as they get modified. But if all you need is to validate some data, or ensure that some hidden columns or logging tables are populated…. I think you’ll find that a stored procedure does the job more efficiently and removes the magic aspect. Furthermore, writing a stored procedure is easy to test; simply set up some mock data and run the stored procedure, verify the results is what you expected. I hope you are using a testing framework like tSQLt.
And it’s important to note that it’s usually more efficient to use database constraints than a trigger. So if you just need to validate that a value is valid in another table, use a foreign key constraint. Validating that a value is within certain range calls for a check constraint. Those should be your default choice for those kind of validations.
So when will we actually need a trigger?
It boils down to cases where you really want the business logic to be in the SQL layer. Maybe because you have multiple clients in different programming languages doing inserts/updates to a table. It would be very messy to duplicate the business logic across each client in their respective programming language and this also means more bugs. For scenarios where it’s not practical to create a middle tier layer, triggers is your best course of action for enforcing the business rule that cannot be expressed as a constraint.
To use an example specific to Access. Suppose we want to enforce business logic when modifying data via the application. Maybe we have multiple data entry forms bound to one same table, or maybe we need to support complex data entry form where multiple base tables must participate in the edit. Maybe the data entry form needs to support non-normalized entries which we then re-compose into normalized data. In all those cases, we could just write VBA code but that can be hard to maintain and validate for all cases. Triggers helps us to move the logic out of VBA and into T-SQL. The data-centric business logic generally is best placed close to the data as possible.
Rule #3: Trigger must be set-based, not row-based
By far the most common mistake made with a trigger is to make it run on rows. Often we see code similar to this:
--Bad code! Do not use! CREATE TRIGGER dbo.SomeTrigger ON dbo.SomeTable AFTER INSERT AS BEGIN DECLARE @NewTotal money; DECLARE @NewID int; SELECT TOP 1 @NewID = SalesOrderID, @NewTotal = SalesAmount FROM inserted; UPDATE dbo.SalesOrder SET OrderTotal = OrderTotal + @NewTotal WHERE SalesOrderID = @SalesOrderID END;
The giveaway should be the mere fact that there was a SELECT TOP 1 off a table inserted. This will only work as long we insert only one row. But when it’s more than one rows, then what happens to those unlucky rows that came 2nd and after? We can improve on that by doing something similar to this:
--Still bad code! Do not use! CREATE TRIGGER dbo.SomeTrigger ON dbo.SomeTable AFTER INSERT AS BEGIN MERGE INTO dbo.SalesOrder AS s USING inserted AS i ON s.SalesOrderID = i.SalesOrderID WHEN MATCHED THEN UPDATE SET OrderTotal = OrderTotal + @NewTotal ; END;
This is now set-based and thus much improved but this still has other problems which we’ll see in next few rules…
Rule #4: Use a view instead.
A view can have a trigger attached to it. This gives us the advantage of avoiding problems associated with a table triggers. We would be able to easily bulk import clean data into the table without having to disable any triggers. Furthermore, a trigger on view make it an explicit opt-in choice. If you have security-related functionalities or business rules that necessitates the running of triggers, you can simply revoke the permissions on the table directly and thus funnel them toward to the new view instead. That ensures that you will go through the project and note where updates to the table is needed so that you can then track them for any possible bugs or issues.
The downside is that a view can only have a INSTEAD OF triggers attached, which means you must explicitly perform the equivalent modifications on the base table yourself within the trigger. However, I tend to think it’s better that way because it also ensure that you know exactly what the modification will be, and thus give you same level of control that you normally have within a stored procedure.
Rule #5: The trigger should be dumb simple.
Remember the comment about debugging and testing a stored procedure? The best favor we can do to ourselves is to keep the business logic in a stored procedure and have the trigger invoke it instead. You should never write business logic directly into the trigger; that’s effectively pouring concrete on the database. It is now frozen to the shape and it can be problematic to adequately test the logic. Your testing harness now must involve some modification to the base table. This is not good for writing simple and repeatable tests. This should be the most complicated as your trigger should be allowed to be:
CREATE TRIGGER [dbo].[SomeTrigger] ON [dbo].[SomeView] INSTEAD OF INSERT, UPDATE, DELETE AS BEGIN DECLARE @SomeIDs AS SomeIDTableType --Perform the merge into the base table MERGE INTO dbo.SomeTable AS t USING inserted AS i ON t.SomeID = i.SomeID WHEN MATCHED THEN UPDATE SET t.SomeStuff = i.SomeStuff, t.OtherStuff = i.OtherStuff WHEN NOT MATCHED THEN INSERT ( SomeStuff, OtherStuff ) VALUES ( i.SomeStuff, i.OtherStuff ) OUTPUT inserted.SomeID INTO @SomeIDs(SomeID); DELETE FROM dbo.SomeTable OUTPUT deleted.SomeID INTO @SomeIDs(SomeID) WHERE EXISTS ( SELECT NULL FROM deleted AS d WHERE d.SomeID = SomeTable.SomeID ) AND NOT EXISTS ( SELECT NULL FROM inserted AS i WHERE i.SomeID = SomeTable.SomeID ); EXEC dbo.uspUpdateSomeStuff @SomeIDs; END;
The first part of the trigger is to basically perform the actual modifications on the base table because it’s an INSTEAD OF trigger, so we must perform all the modifications which will be different depending on the tables we need to manage. It is worth emphasizing that modifications should be mainly verbatim. We don’t re-calculate or transform any of the data. We save all that extra work at the end, where all we are doing within the trigger is populating a list of records that were modified by the trigger and providing to a stored procedure using a table-valued parameter. Note that we are not even considering what records were changed nor how it was changed. All that can be done within the stored procedure.
Rule #6: The trigger should be idempotent whenever possible.
Generally speaking, the triggers MUST be idempotent. This applies regardless whether it’s a table-based or a view-based trigger. It applies especially to those that needs to modify the data on the base tables from where trigger is monitoring. Why? Because if humans are modifying the data that will get picked up by the trigger, they might realize they made a mistake, edited it again or maybe simply edit the same record and save it 3 times. They won’t be happy if they find that the reports changes every time they make an edit that isn’t supposed to modify the output for the report.
To be more explicit, it might be tempting to try and optimize the trigger by doing something similar to this:
WITH SourceData AS ( SELECT OrderID, SUM(SalesAmount) AS NewSaleTotal FROM inserted GROUP BY OrderID ) MERGE INTO dbo.SalesOrder AS o USING SourceData AS d ON o.OrderID = d.OrderID WHEN MATCHED THEN UPDATE SET o.OrderTotal = o.OrderTotal + d.NewSaleTotal;
We get to avoid re-calculating the new total by just reviewing the modified rows in the inserted table, right? But when the user edits the record to correct a typo in the customer’s name, what will happen? We end up with a bogus total, and the trigger is now working against us.
By now, you should see why the rule #4 helps us out by pushing out only the primary keys to the stored procedure, rather than trying to pass any data into the stored procedure or doing it directly inside the trigger as the sample would have done.
Instead, we want to have some code similar to this within a stored procedure:
CREATE PROCEDURE dbo.uspUpdateSalesTotal ( @SalesOrders SalesOrderTableType READONLY ) AS BEGIN WITH SourceData AS ( SELECT s.OrderID, SUM(s.SalesAmount) AS NewSaleTotal FROM dbo.SalesOrder AS s WHERE EXISTS ( SELECT NULL FROM @SalesOrders AS x WHERE x.SalesOrderID = s.SalesOrderID ) GROUP BY OrderID ) MERGE INTO dbo.SalesOrder AS o USING SourceData AS d ON o.OrderID = d.OrderID WHEN MATCHED THEN UPDATE SET o.OrderTotal = d.NewSaleTotal; END;
Using the @SalesOrders, we still can selectively update only the rows that were affected by the trigger, and we also can recalculate the new total altogether and make it the new total. So even if the user made a typo on the customer name and edited it, each save will yield the same result for that row.
More importantly, this approach also provides us with an easy way to fix up the totals. Suppose we have to do bulk import, and the import doesn’t contain the total so we must calculate it ourselves. We can write the stored procedure to write to the table directly. We can then invoke the above stored procedure passing in the IDs from the import, and we’re all good. Thus, the logic we use isn’t tied up in the trigger behind the view. That helps when the logic is unnecessary for the bulk import we’re performing.
If you find yourself having problem making your trigger idempotent, it’s a strong indication that you might need to be using a stored procedure instead and calling it directly from your application instead of relying on triggers. One notable exception to this rule is when the trigger is primarily meant to be an auditing trigger. In this case, you do want to write a new row to the audit table for each edits, including all the typos that the user makes. This is OK because in that case, there is no changes to the data that the user is interacting with. From the user’s POV, it’s still the same result. But whenever the trigger needs to manipulate the same data that the user is working with, it’s much better when it’s idempotent.
Hopefully by now, you can see how much more difficult it can be to design a well-behaved trigger. For that reason, you should carefully consider whether you can avoid it altogether and use direct invocations with stored procedure. But if you’ve concluded that you must have triggers to manage the modifications made via views, I hope that the rules will help you. Making the trigger set-based is easy enough with some adjustments. Making it idempotent usually requires more thoughts in how you will implement your stored procedures.
If you have any more suggestions or rules to share, fire away in the comments!
Hi Ben …
Glad you agree Triggers are appropriate for audit trails. A project I’m currently doing at work does require audit trails on a few tables (we are on SQL Server 14). I’ve spent the last week or so completely researching Triggers and examples of how to do audit trails. I think I have a handle on it now 🙂
You know what they say in come circles… Keep Your Finger Off The Trigger Until You Are Ready To Shoot 🙂
I recently ran across a case where I was forced to consider a trigger in lieu of modifying the code in a web page.
The specific request was “When someone includes a value in Field A, I want to have that same value appear in Field B as a concatenation with whatever is already there.” (Think “Additional Notes for the tester.”)
All simple until you realize that Field A is exposed in one set of web pages, and Field B in a different set of pages.
An After Update trigger would do the trick simply and require no changes to any c# code in any of those web pages.,
Modifying multiple aspx pages might run into a wad of new code in several places, along with the additional testing to ensure regressions are introduced along the way.
I wonder how you (and your clients’ budgets) feel about these two options, given our mutual distrust of triggers in general.
My finger is hovering over the trigger, pending your response.
Now, let’s not get trigger-happy, George. 😉
While I do agree that an AFTER trigger on a table would be cheaper to implement, I should point out that creating a view that has all the fields from the table is pretty simple change since we should only need to change the recordsource, and the rest of the code should remain unchanged. Your tests would be then limited to testing the trigger logic, which you still have to do anyway in both cases. So creating a new view that’s identical to the table should not require that much more time. Unless you have 1000 web pages, of course. 🙂 But for most cases, it’s only a couple more hours.
If the budget prohibits you from implementing a view and attaching a trigger to it, at least make sure it’s a stored procedure that you can call directly using a table type because that gives you a way to easily test the logic you need to accomplish and apply it to only the rows that you specify (which the trigger will also provide).
How do you feel about triggers for an audit log? Would you still recommend calling a stored procedure to track the before and after row values?
That was an exception I cited when I discussed the idempotent rule since by definition, an audit trigger cannot be idempotent. When we are working with prior versions that’s not an Enterprise version, that is likely a reasonable course of action. With SQL Server 2016 and newer, you might want to look into using audit instead of triggers.
SQL Server Audit
Excellent article! As fate would have it, I was pondering this very issue not too long ago. I shelved the idea because I had bigger fish to fry. I was getting ready to re-visit it this week and *BAM* this article shows up in my inbox today.
Thanks for sharing, this has been a HUGE help.
Glad it was timely! When you do finish the project, I’d love to hear any suggestions you might have discovered in your scenarios. 🙂