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?
First thing's first, let's setup a demo.
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);
Now, let's add a trigger that modifies data as it is inserted (logs who inserted the record) and then insert 5 more rows.
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);
PRINT 'Live Table'; SELECT * FROM TemporalTest ORDER BY colint; GO PRINT 'Historical Table'; SELECT * FROM TemporalTestHistory ORDER BY colint;
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.
INSERT INTO TemporalTestHistory VALUES (1,'Unknown User', '2016-06-13 16:51:19.9473703', '2016-06-13 16:51:19.9473703');
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!
I hope that you found these two tidbits helpful!
Links to other posts in this series