SQL Server时态表不为事务内的更新和删除保留删除历史记录

-- 启用系统版本控制开关
ALTER TABLE YourTemporalTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = YourHistoryTable, DATA_CONSISTENCY_CHECK = ON));

-- 禁用系统版本控制开关
ALTER TABLE YourTemporalTable SET (SYSTEM_VERSIONing = OFF);

CREATE TRIGGER trg_PreventHistoryInsertDelete
ON YourTemporalTable
AFTER UPDATE, DELETE
AS
BEGIN
  IF EXISTS(SELECT 1 FROM inserted) OR EXISTS(SELECT 1 FROM deleted)
  BEGIN
    RAISERROR('Updates and deletes in the current table are not allowed to affect the history table.', 16, 1);
    ROLLBACK TRANSACTION;
  END
END;

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 更新或删除当前表中的记录
UPDATE YourTemporalTable SET Column1 = Value1 WHERE Condition;
DELETE FROM YourTemporalTable WHERE Condition;

COMMIT TRANSACTION;