審核/跟踪更改

我想跟踪 SQL Server 2012 上的更改，並將其保存 5 天。

我想獲取每個插入/更新/刪除操作在 SQL Server 上發生的時間並保存新的插入行（插入操作）/舊 + 新值（用於更新操作）/刪除的行（用於刪除操作）。

我已經搜尋了很多，但我找不到解決方案。

如果您需要對整個數據庫進行更改跟踪，則可以考慮實施SQL Server Change Tracking。

如果您想跟踪對單個表的更改，您可以考慮滾動您自己的解決方案，如下面我創建的範例所示：

我們要跟踪所做更改的範例表：

CREATE TABLE dbo.SomeData
(
   ID int NOT NULL
       CONSTRAINT PK_SomeData
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , SomeVal varchar(100) NOT NULL
   , SomeDate datetime NOT NULL
);

我們將跟踪更改的表：

CREATE TABLE dbo.SomeData_Changes
(
   ChangeID bigint NOT NULL
       CONSTRAINT PK_SomeData_Changes
       PRIMARY KEY CLUSTERED
       IDENTITY(1,1)
   , ChangeGroup int NOT NULL
   , ChangeDate datetime
       CONSTRAINT DF_SomeData_Changes_ChangeDate
       DEFAULT ((GETDATE()))
   , OpType varchar(1) NOT NULL
   , ID int NOT NULL
   , SomeVal varchar(100) NOT NULL
   , SomeDate datetime NOT NULL
);

我們需要的變更跟踪基礎設施包括將變更組合在一起的序列：

CREATE SEQUENCE dbo.SomeData_Changes_Group
AS int
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 1000;
GO

這是一個觸發器，將跟踪對錶所做的所有更改SomeData：

CREATE TRIGGER SomeData_ChangeTracker
ON dbo.SomeData
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
   DECLARE @Group int;
   SET @Group = NEXT VALUE FOR dbo.SomeData_Changes_Group;

   IF EXISTS (
       SELECT 1
       FROM deleted
       )
   BEGIN
       INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
       SELECT 'D'
           , @Group
           , d.ID
           , d.SomeVal
           , d.SomeDate
       FROM deleted d
   END

   IF EXISTS (
       SELECT 1
       FROM inserted
       )
   BEGIN
       INSERT INTO dbo.SomeData_Changes (OpType, ChangeGroup, ID, SomeVal, SomeDate)
       SELECT 'I'
           , @Group
           , i.ID
           , i.SomeVal
           , i.SomeDate
       FROM inserted i
   END
   PRINT N'In trigger';
END
GO

在這裡，我們測試更改跟踪器：

INSERT INTO dbo.SomeData (SomeVal, SomeDate)
VALUES ('test row 1', GETDATE());

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║ ChangeDate ║ OpType ║ ID ║ SomeVal ║ SomeDate ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║ 1 ║ 1 ║ 2018-03-02 10:17:21.710 ║ I ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

更新：

UPDATE dbo.SomeData
SET SomeVal = 'Test Row 1'
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║ ChangeDate ║ OpType ║ ID ║ SomeVal ║ SomeDate ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║ 1 ║ 1 ║ 2018-03-02 10:17:21.710 ║ I ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
║ 2 ║ 2 ║ 2018-03-02 10:17:21.710 ║ D ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
║ 3 ║ 2 ║ 2018-03-02 10:17:21.710 ║ I ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

上面的輸出顯示ChangeGroup“2”，即更新。它由“刪除”和“插入”兩個部分組成，即在 SQL Server 的幕後實際完成了多少表更新。

一個刪除：

DELETE 
FROM dbo.SomeData
WHERE ID = 1;

SELECT *
FROM dbo.SomeData_Changes;

╔══════════╦═════════════╦═════════════════════════╦════════╦════╦════════════╦═════════════════════════╗
║ ChangeID ║ ChangeGroup ║ ChangeDate ║ OpType ║ ID ║ SomeVal ║ SomeDate ║
╠══════════╬═════════════╬═════════════════════════╬════════╬════╬════════════╬═════════════════════════╣
║ 1 ║ 1 ║ 2018-03-02 10:17:21.710 ║ I ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
║ 2 ║ 2 ║ 2018-03-02 10:17:21.710 ║ D ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
║ 3 ║ 2 ║ 2018-03-02 10:17:21.710 ║ I ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
║ 4 ║ 3 ║ 2018-03-02 10:17:21.713 ║ D ║ 1 ║ 測試行 1 ║ 2018-03-02 10:17:21.703 ║
╚══════════╩═════════════╩═════════════════════════╩════════╩════╩════════════╩═════════════════════════╝

刪除是ChangeGroup上面的3。

清理：

IF OBJECT_ID(N'dbo.SomeData', N'U') IS NOT NULL
BEGIN
   DROP TRIGGER SomeData_ChangeTracker;
   DROP TABLE dbo.SomeData;
END
IF OBJECT_ID(N'dbo.SomeData_Changes', N'U') IS NOT NULL
BEGIN
   DROP TABLE dbo.SomeData_Changes;
   DROP SEQUENCE dbo.SomeData_Changes_Group;
END
GO

您可以使用每天午夜執行以下操作的 SQL Server 代理作業從更改跟踪表中修剪行：

DELETE 
FROM dbo.SomeData_Changes
WHERE dbo.SomeData_Changes.ChangeDate < DATEADD(DAY, -5, GETDATE());

社區維基回答：

您可以使用SQL Server 數據庫審核。

SQL Server 的所有版本都支持伺服器級審核。數據庫級審計僅限於 Enterprise、Developer 和 Evaluation 版本。有關詳細資訊，請參閱SQL Server 2012 版本支持的功能。

對於更現代的版本：

從 SQL Server 2016 SP1 開始，所有版本都支持數據庫級別審核。在此之前，數據庫級審計僅限於 Enterprise、Developer 和 Evaluation 版本。

文件中的這些頁面是概述。左側導航連結到更多資訊，頁面中還有更多詳細資訊的連結。

引用自：https://dba.stackexchange.com/questions/199210