SQL 2016 - Temporal Tables - Triggers and Historical Data Tidbits

  • Article

Hello again and welcome back to the series on Temporal Tables!

Today we will take a look at two common questions.  What happens when I put a trigger on a Temporal Table and can I back populate the historical table?

 

Setup

First thing's first, let's setup a demo.

[sql]CREATE TABLE [dbo].[TemporalTest](
[colint] [int] NOT NULL,
[colInsertedBy] [sysname] NULL,
[period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED
(
[colint] ASC
)ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([period_start], [period_end])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON )
)
GO
INSERT INTO TemporalTest (colint) VALUES (1), (2), (3), (4), (5);[/sql]

 

Triggers

Now, let's add a trigger that modifies data as it is inserted (logs who inserted the record) and then insert 5 more rows.

[sql]CREATE TRIGGER trg_TemporalInsertTest
ON  TemporalTest
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
    UPDATE TemporalTest
SET colInsertedBy = SUSER_NAME()
WHERE colint  IN (SELECT colint
FROM inserted);
END;
GO
INSERT INTO TemporalTest (colint) VALUES (6), (7), (8), (9), (10);[/sql]

Next, let's see how the data looks insert the live table and the historical table (for clarity in my screenshot below, I ran the results to text so that we had a header of which table the data came from and the date columns are cut off because they are so far to the right).

[sql]PRINT 'Live Table';
SELECT * FROM TemporalTest ORDER BY colint;
GO
PRINT 'Historical Table';
SELECT * FROM TemporalTestHistory ORDER BY colint;[/sql]

So we can see that the trigger does still work as it was intended (I censored out the login) and we have in the history table the original values that were inserted before the trigger fired.

Something to keep in mind, is that if you use triggers, they will cause additional records in the historical table which could cause reading the data to see how values changed over time to be more difficult due to more records.

 

Loading older historical data

Next, let's try to manually insert a record into the historical table.

[sql]INSERT INTO TemporalTestHistory VALUES (1,'Unknown User', '2016-06-13 16:51:19.9473703', '2016-06-13 16:51:19.9473703');[/sql]

This is the message that I receive:

Msg 13559, Level 16, State 1, Line 28
Cannot insert rows in a temporal history table 'TemporalTables.dbo.TemporalTestHistory'.

We cannot manually insert data into the historical table, but you can load it into another user table and create a view to combine the Temporal Table's historical table and your custom historical table!

 

The End

I hope that you found these two tidbits helpful!

Links to other posts in this series SQL 2016 – Temporal Tables – What are they and how do you set one up? SQL 2016 – Temporal Tables – How do you drop a Temporal Table? SQL 2016 – Temporal Tables – Can you change the schema?

Comments

  • Anonymous
    July 25, 2017
    Thank you! Many of us use triggers for updating inline audit fields in rows, and it is definitely good to know that the behavior will be as expected!