If a trigger runs an update will it ALWAYS have the same timestamp for a temporal table?

2 votes
2 answers
318 views
                          Background
-
Here is an example near to what I am working with:

    CREATE TABLE sandboxTesting.TemporalTest (    
        GroupNumber VARCHAR(25) NOT NULL,
        StartEffectiveWhen DATE NOT NULL,
        EndEffectiveWhen DATE NULL,
        ModifiedWhen DATETIME NULL,
        IsReady BIT NOT NULL DEFAULT 0,
        RowValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
        RowValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
        PERIOD FOR SYSTEM_TIME (RowValidFrom, RowValidTo),
        CONSTRAINT PK_TemporalTest PRIMARY KEY CLUSTERED  
        (
    	    GroupNumber, StartEffectiveWhen
        )
    ) WITH (SYSTEM_VERSIONING=ON (HISTORY_TABLE=sandboxTesting.TemporalTestHistory))
    GO 
    
    CREATE TRIGGER sandboxTesting.OnModify ON sandboxTesting.TemporalTest AFTER UPDATE AS
    BEGIN
        
        UPDATE  temporalTst
        SET     temporalTst.IsReady = 0,
                temporalTst.ModifiedWhen = GETDATE()
        FROM    sandboxTesting.TemporalTest temporalTst
                JOIN deleted del
                    ON del.GroupNumber = temporalTst.GroupNumber
                    AND del.StartEffectiveWhen = temporalTst.StartEffectiveWhen
        WHERE   -- All business columns go here with OR statements in between them.
                -- The idea is that if anything changes except the IsReady flag, then we 
                -- set the IsReady back to false.  (IsReady has to be set by itself)
                del.EndEffectiveWhen  temporalTst.EndEffectiveWhen 
                OR (del.EndEffectiveWhen IS NULL AND temporalTst.EndEffectiveWhen IS NOT NULL)
                OR (del.EndEffectiveWhen IS NOT NULL AND temporalTst.EndEffectiveWhen IS NULL)
    END
    GO
    
    -- Insert new test
    INSERT INTO [sandboxTesting].[TemporalTest] ([GroupNumber], [StartEffectiveWhen], [EndEffectiveWhen],  [ModifiedWhen])
    VALUES ('12345', '2024-01-1', NULL, NULL)
    GO
    
    -- Set is as ready 
    UPDATE  sandboxTesting.TemporalTest
    SET     IsReady = 1
    WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
    GO 
    
    -- Change the End date
    UPDATE  sandboxTesting.TemporalTest
    SET     EndEffectiveWhen = '2024-09-02'
    WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
            
    -- Set the new end date as ready for billing.
    UPDATE  sandboxTesting.TemporalTest
    SET     IsReady = 1
    WHERE   GroupNumber = '12345' AND StartEffectiveWhen = '2024-01-1'
    GO 
    
    
    -- Select the Data
    SELECT * FROM sandboxTesting.TemporalTest for SYSTEM_TIME ALL 
    ORDER BY GroupNumber, StartEffectiveWhen desc, RowValidFrom DESC, RowValidTo DESC, ModifiedWhen desc
    
    -- Select the Raw Data (for comparison)
    SELECT * FROM sandboxTesting.TemporalTest
    UNION ALL 
    SELECT * FROM sandboxTesting.TemporalTestHistory
    ORDER BY GroupNumber, StartEffectiveWhen desc, RowValidFrom DESC, RowValidTo DESC, ModifiedWhen desc

When I run this, this is the first result:

|GroupNumber|StartEffectiveWhen|EndEffectiveWhen|ModifiedWhen|IsReady|RowValidFrom|RowValidTo|
|:----|:----|:----|:----|:----|:----|:----|
|12345|2024-01-01|2024-09-02|2024-08-29 17:15:28.587|1|2024-08-29 23:15:28.5764223|9999-12-31 23:59:59.9999999|
|12345|2024-01-01|NULL|NULL|1|2024-08-29 23:15:28.5295658|2024-08-29 23:15:28.5764223|
|12345|2024-01-01|NULL|NULL|0|2024-08-29 23:15:28.4826980|2024-08-29 23:15:28.5295658|


And the second set of output looks like this:

|GroupNumber|StartEffectiveWhen|EndEffectiveWhen|ModifiedWhen|IsReady|RowValidFrom|RowValidTo|
|:----|:----|:----|:----|:----|:----|:----|
|12345|2024-01-01|2024-09-02|2024-08-29 17:15:28.587|1|2024-08-29 23:15:28.5764223|9999-12-31 23:59:59.9999999|
|12345|2024-01-01|2024-09-02|2024-08-29 17:15:28.587|0|2024-08-29 23:15:28.5764223|2024-08-29 23:15:28.5764223|
|12345|2024-01-01|2024-09-02|NULL|1|2024-08-29 23:15:28.5764223|2024-08-29 23:15:28.5764223|
|12345|2024-01-01|NULL|NULL|1|2024-08-29 23:15:28.5295658|2024-08-29 23:15:28.5764223|
|12345|2024-01-01|NULL|NULL|0|2024-08-29 23:15:28.4826980|2024-08-29 23:15:28.5295658|


This is different because the first query result uses the for SYSTEM_TIME ALL clause, while the second one just queries the raw data.

The difference is that, in the first data set, the second and third rows of the second data set have been filtered out.  They have been removed because the second and third rows have a start date and end date that are the same.  (Basically saying those rows were never really in effect.)

Question
-

What I need to know is, can I depend on this "zero time difference" always being there for data that was updated via an AFTER trigger?  (I need to write some queries that will fail if this is not the case.) 

What I mean by that is this:  If my server was being hammered by thousands of queries all doing crazy amounts of IO and calculations, would the RowValidFrom and RowValidTo values for the second and third rows of the second dataset still have a difference of 0?

Put another way, are these values the same because of transaction logic?  Or are they the same because my server is fast and not really under any pressure right now?

                        
Asked by Vaccano (2550 rep)
Aug 29, 2024, 11:25 PM
Last activity: Aug 30, 2024, 12:26 PM
If a trigger runs an update will it ALWAYS have the same timestamp for a temporal table?

Related Questions
