Temporal Table Columns

Question

Temporal Table Columns

December 31, 2019 at 5:05 am

This was removed by the editor as SPAM

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

Crud. I was afraid of that. The dynamic SQL cannot see the INSERTED/DELETED tables, either. I could copy the INSERTED table to a temporary table or table variable but that's not going to buy me anything here and would be terrible for performance to boot.

Anyway, thanks for the help, Scott. MS didn't make it easy. If we use an INSTEAD OF trigger to handle a main and sister table to keep LOBs out of the main table and, so, out of the history table, we're going to run into all the same problems with dynamic SQL unless I copy the INSERTED/DELETED tables into another temporary structure.

Heh... I might have some performance testing to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

Bah! It's just not worth it. You can't add a default column to Temporal Tables. You can't add a column to the History table without adding it to the main table (you can, but it won't work as a temporal table anymore {and if fails silently to boot!} and you have a bunch of strangely named unused tables be added to your database). You can't have columns in the main table that aren't in the history table and that wouldn't work for what I want to do anyway. You can't get INSTEAD OF triggers to work correctly for UPDATEs because you can't reference the INSERTED/DELETED tables from within dynamic SQL in the trigger (I'm not an Oracle user but "My kingdom for an Oracle BEFORE trigger!!!). Besides, if you do that, you might as well write your own audit trigger that does everything you want it to the first time including SCD 6, which isn't difficult to do. Throw in a UNION ALL view and you can get the point-in-time reporting, as well.

Temporal tables are easy to setup and use for "normal" stuff but if you want to avoid LOBs in your history table or add anything only to the history table (like a column that defaults to ORIGINAL_LOGIN), forget about using System-Versioned Temporal Tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101130 More actions · Answer 3

OK, so I guess the AFTER trigger would have to use the deleted table to check for UPDATEs vs. just relying on the UPDATE() function.

CREATE TRIGGER dbo.TriggerSource_AFTER_UPDATE
    ON dbo.TriggerSource
  AFTER UPDATE
    AS
SELECT  RowNum
        ,ColA
        ,ColB
        ,ColC
        ,ColA_Updated = CASE WHEN I.ColA <> D.ColA OR 
            ((I.ColA IS NULL AND D.ColA IS NOT NULL) OR (I.ColA IS NOT NULL AND D.ColA IS NULL))
            THEN 'Updated' ELSE '' END
        ,[...]
  FROM INSERTED I
  INNER JOIN DELETED D ON I.$IDENTITY = D.$IDENTITY

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".