Faking Temporal Tables with Triggers

Published on: 2018-09-11

This post is a response to this month’s T-SQL Tuesday #106 prompt by Steve Jones.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month’s topic asks to share our experiences with triggers in SQL Server.


Triggers are something that I rarely use.  I don’t shy away from them because of some horrible experience I’ve had, but rather I rarely have a good need for using them.

The one exception is when I need a poor man’s temporal table.

Temporal Table <3

When temporal tables were added in SQL Server 2016 I was quick to embrace them.

A lot of the data problems I work on benefit from being able to view what data looked like at a certain point back in time, so the easy setup and queriability of temporal tables was something that I immediately loved.

No System Versioning For You

Sometimes I can’t use temporal tables though, like when I’m forced to work on an older version of SQL Server.

Now, this isn’t a huge issue; I can still write queries on those servers to achieve the same result as I would get with temporal tables.

But temporal tables have made me spoiled.  They are easy to use and I like having SQL Server manage my data for me automatically.

Fake Temporal Tables With Triggers

I don’t want to have to manage my own operational versus historical data and write complicated queries for “point-in-time” analysis, so I decided to fake temporal table functionality using triggers.

Creating the base table and history table are pretty similar to that of a temporal table, just without all of the fancy PERIOD and GENERATED ALWAYS syntax:

The single UPDATE,DELETE trigger is really where the magic happens though.  Everytime a row is updated or deleted, the trigger inserts the previous row of data into our history table with correct datetimes:

The important aspect to this trigger is that we always join our dbo.Birds table to our inserted and deleted tables based on the primary key, which is the Id column in this case.

If you try to insert/update/delete data from the dbo.Birds table, the dbo.BirdsHistory table will be updated exactly like a regular temporal table would:

If you run each of those batches one at a time and check both tables, you’ll see how the dbo.BirdsHistory table keeps track of all of our data changes.

Now seeing what our dbo.Birds data looked like at a certain point-in-time isn’t quite as easy as a system versioned table in SQL Server 2016, but it’s not bad:

Real Performance

One reason many people loath triggers is due to their potential for bad performance (particular when many triggers get chained together).

I wanted to see how this trigger solution compares to an actual temporal table.  While searching for good ways to test this difference, I found that Randolph West has done some testing on trigger-based temporal tables.  While our solutions are different, I like their performance testing methodology: view the transaction log records for real temporal tables and compare them to those of the trigger-based temporal tables.

I’ll let you read the details of how to do the comparison test in their blog post but I’ll just summarize the results of my test: the trigger based version is almost the same as a real system versioned temporal table.

Because of how I handle updating the SysStartTime column in my dbo.Birds table, I get one more transaction than a true temporal table:

You could make the trigger solution work identical to the true temporal table (as Randolph does) if you are willing to make application code changes to populate the SysStartTime column on insert into dbo.Birds.

Conclusion

For my purposes, the trigger-based temporal table solution has a happy ending.  It works for the functionality that I need it for and prevents me from having to manage a history table through some other process.

If you decide to use this in your own pre-2016 instances, just be sure to test the functionality you need; while it works great for the purposes that I use temporal tables for, your results may vary if you need additional functionality (preventing truncates on the history table, defining a retention period for the history, etc… are all features not implemented in the examples above).

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

9 thoughts on “Faking Temporal Tables with Triggers”

  1. I think your solution can be made a good bit more efficient by using two separate triggers. You are doing queries on both sides of the coin (UPDATE/DELETE) that don’t need to be done when the other side is in effect.

    1. Thanks Kevin!

      The updating of dbo.Birds doesn’t need to happen on DELETEs so removing it would save a little bit of time. The insert into dbo.BirdsHistory always runs though, so that means I would have this query duplicated in two separate triggers.

      Without testing I don’t know if a well-indexed UPDATE on no rows is better/worse than executing two separate triggers, but I’m leaning towards your suggestion being the better performer.

      1. As for two triggers, you wouldn’t be executing two separate triggers – ever – because they fire on different actions. Doing the minimum work necessary for all SQL Server operations simply must be more efficient than anything else.

        For the INSERT, go a step deeper. Would the INSERT statement be the same for an UPDATE as it would be for a DELETE, given that they would occur in their own specific trigger? The answer is no, and both INSERT statements coded specifically for their operations would be more efficient.

        Hmm, does your code even work properly for a DELETE? You have this code in an AFTER DELETE trigger:

        INSERT INTO dbo.BirdsHistory
        SELECT d.Id, d.BirdName, d.SightingCount,d.SysStartTime,ISNULL(b.SysStartTime,@CurrentDateTime)
        FROM
        dbo.Birds b
        RIGHT JOIN deleted d
        ON b.Id = d.Id

        If you DELETE a row in an AFTER trigger, the row will not be in the base table when the trigger code is executing. How can you get a history row to insert if the row is missing from the Birds table?

        1. Thanks Kevin, the testing I did indicates that the code works.

          Both FOR DELETE and AFTER DELETE triggers populate the deleted table. Here’s a simple test that shows that behavior:

          CREATE TABLE dbo.TriggerTest
          (
          Id int
          );
          CREATE TRIGGER OnDeleteTrigger ON dbo.TriggerTest
          FOR DELETE
          AS
          BEGIN
          SET NOCOUNT ON;

          SELECT * FROM deleted
          END

          CREATE TRIGGER AfterDeleteTrigger ON dbo.TriggerTest
          AFTER DELETE
          AS
          BEGIN
          SET NOCOUNT ON;

          SELECT * FROM deleted
          END

          INSERT INTO dbo.TriggerTest VALUES (1);
          DELETE FROM dbo.TriggerTest;

  2. Wait, I saw b.BirdName – my apologies! 🙂

    So now it is simply a matter of being inefficient. On DELETE your INSERT should look like this:

    INSERT INTO dbo.BirdsHistory
    SELECT d.Id, d.BirdName, d.SightingCount,d.SysStartTime,@CurrentDateTime
    FROM deleted d

    That is clearly more efficient than your original query. Once again two triggers is more efficient than one. And I am pretty sure that on UPDATE you can change the RIGHT JOIN on DELETED to an INNER JOIN, because you are guaranteed to have matching row(s) there.

  3. Absolutely LOVED the video! Makes a good point even better well-taken. Thank you for taking the time to post this, as I will need to employ a similar solution for older SQL Server versions at some point in the hopefully near future.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.