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.

 

Wrapping up

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!