SQL Server-更新表历史记录

CM2K 发表于 Dev

CM2K

考虑以下来自#myTempTwo的数据示例

sno     |   name        |budget |NameTrim   |TravelCost|    Expense
1       |Local Travel   |123    |Travel     |246       |    NULL
2       |Global Travel  |123    |Travel     |246       |    NULL
3       |Local Expense  |12     |Expense    |NULL      |    24
4       |Curent Expense |12     |Expense    |NULL      |    24

场景TravelCost或Expensenull为空的情况下，使用值更新。假设1表示费用，2表示travelCost。

现在说更新：

update #myTempTwo 
 set TravelCost = '2' where TravelCost is null
 update #myTempTwo 
 set Expense = '1' where Expense is null

这将产生：

    sno     |   name        |budget |NameTrim   |TravelCost|    Expense
    1       |Local Travel   |123    |Travel     |246       |    1
    2       |Global Travel  |123    |Travel     |246       |    1
    3       |Local Expense  |12     |Expense    |2         |    24
    4       |Curent Expense |12     |Expense    |2         |    24

我能以某种方式查看原始数据吗（从运行更新之前开始，而没有将其保存在其他表中。我正在寻找某些“撤消”或“查看历史记录”或某些“临时快照”吗？（使用SQL Server 2012）

如果是，我该怎么办？

乔基·纳库里（Giorgi Nakeuri）

这是触发器和历史记录表的示例：

CREATE TABLE OriginaTable(ID INT IDENTITY, Price MONEY)
GO

CREATE TABLE HistoryTable(ID INT IDENTITY, OriginalID INT, Price MONEY, CreatedDate DATETIME)
GO

CREATE TRIGGER trOriginaTable ON OriginaTable
FOR UPDATE
AS
BEGIN
  IF UPDATE(Price)
  BEGIN
        INSERT INTO dbo.HistoryTable
                ( OriginalID, Price, CreatedDate )
                SELECT ID, Price, GETDATE() FROM Deleted
  END
END
GO

INSERT INTO OriginaTable VALUES(NULL)
GO

UPDATE dbo.OriginaTable SET Price = 100 WHERE ID = 1
UPDATE dbo.OriginaTable SET Price = 10 WHERE ID = 1
UPDATE dbo.OriginaTable SET Price = 200 WHERE ID = 1

SELECT * FROM dbo.HistoryTable

输出：

ID  OriginalID  Price   CreatedDate
1   1           NULL    2015-11-05 18:46:49.823
2   1           100.00  2015-11-05 18:46:49.830
3   1           10.00   2015-11-05 18:46:49.833

如您所见，历史记录会保存所有旧值。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。