Update temporal table without updating history

Question

I have a temporal table Person with PersonHistory.

Columns:

[Id], [Name], [DepartmentId], [ModifiedBy], [SysStartTime], [SysEndTime]

When physically deleting a row, I want to persist who deleted the row in ModifiedBy, without adding 2 rows to PersonHistory.

Any ideas on how I can achieve this?

Thanks.

Answer 1

It can be done but it would be a bit hacky. Also you will lose the history of whoever changed the row into its current state (EG: user1 creates the record with name 'jams'. user2 changed the name to 'james'. user 3 deleted the row. In the history you wouldn't see user2's edit from 'jams' to 'james', just user3's deleting of the row with the name 'james') so you are losing a bit off your audit trail

This may work in a trigger, I'm not sure, but if you restricted your delete action for the table in question to a SPROC then it could be done like this:

CREATE PROC [People].[Person_Delete]
(
    @Id INT,
    @DeletedBy VARCHAR(255)
)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

            --===========================================================================================
            --TURN OFF SYSTEM VERSIONING FOR THE TARGET TABLE
            --===========================================================================================
            IF (SELECT temporal_type FROM sys.tables WHERE object_id = OBJECT_ID('People.Person', 'U')) = 2
            BEGIN
                EXEC(N'
                    PRINT(''Deactivating SYSTEM_VERSIONING for People.Person...'')
                    ALTER TABLE People.Person
                    SET (SYSTEM_VERSIONING = OFF)
                    ALTER TABLE People.Person
                    DROP PERIOD FOR SYSTEM_TIME
                ')
            END

            --===========================================================================================
            --UPDATE THE ModifiedBy VALUE
            --===========================================================================================

            UPDATE People.Person
            SET ModifiedBy = @DeletedBy
            WHERE Id = @Id

            --===========================================================================================
            --TURN ON SYSTEM VERSIONING FOR THE TARGET TABLE
            --===========================================================================================
            IF (SELECT temporal_type FROM sys.tables WHERE object_id = OBJECT_ID('People.Person', 'U')) = 0 
            BEGIN 
                EXEC(N' 
                    PRINT(''Activating SYSTEM_VERSIONING for People.Person...'') 
                    ALTER TABLE People.Person 
                    ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) 
                    ALTER TABLE People.Person 
                    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=People.PersonHistory, DATA_CONSISTENCY_CHECK=ON)) 
                ')
            END

            --===========================================================================================
            --DELETE THE RECORD
            --===========================================================================================
            DELETE People.Person
            WHERE Id = @Id

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION

        ;THROW;
    END CATCH
END