SQL筆記：透過觸發Trigger進行資料表新增、修改、刪除之自動化LOG

簡單的說，這篇文章就是在描述如何從一張資料表中，不透過應用程式端監控再分別寫入的方式，使用純粹SQL Server的觸發事件功能，去監控並記錄某張資料表的新增、修改、刪除等動作，並自動化的紀錄（LOG）在另外一張資料表中。

建立主要監控的資料表

其實任何一張資料表都可以被監控，但是一定會損耗效能的，以下是我們要建立的資料表指令，其中的iAutoIndex是主索引鍵（Primary Key），並設為自動增量（Auto Increment），這邊命令這張資料表的名稱是zTable。

USE [Catalog_資料桶名稱]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[zTable]
(
  [iAutoIndex] [int] IDENTITY(1,1) NOT NULL,
  [cName] [nvarchar](50) NOT NULL,
  [dDate] [nvarchar](50) NOT NULL,
  [cNote] [nvarchar](50) NULL,
  CONSTRAINT [PK_zTable] PRIMARY KEY CLUSTERED 
  (
    [iAutoIndex] ASC
  ) WITH
  (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY]
) ON [PRIMARY]
GO

建立監控紀錄的LOG資料表

接下來就是建立監控主資料表所記錄的Log資料表zTable_Log，其中比較重要的欄位iMap2AutoIndex對應的是zTable的iAutoIndex，另外的cOldData與cNewData最後將以XML RAW型態被記錄成檔案備查，所以這邊將欄位型別設定成NVARCHAR(MAX)。

USE [Catalog_資料桶名稱]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[zTable_Log]
(
  [iAutoIndex] [int] IDENTITY(1,1) NOT NULL,
  [cAction] [nvarchar](10) NOT NULL,
  [dAction] [datetime] NOT NULL,
  [iMap2AutoIndex] [int] NOT NULL,
  [cOldData] [nvarchar](max) NULL,
  [cNewData] [nvarchar](max) NULL,
  PRIMARY KEY CLUSTERED 
  (
    [iAutoIndex] ASC
  ) WITH
  (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  )	ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

設計資料表觸發程序

這個步驟是實際的將zTable與zTable_Log進行實際的觸發綁定，日後進行的INSERT, UPDATE, DELETE所有歷程都會被記錄在這張zTable_Log表中。值得一提的是，因為是採用XML RAW的方式被SELECT出來，所以日後zTable若有欄位異動增刪也沒有關係，不用再特地回來修改觸發程序的程式碼。

-- 建立資料表觸發程序
CREATE
  TRIGGER zTable自動觸發紀錄
  ON      zTable
  AFTER   INSERT, UPDATE, DELETE
AS
BEGIN
  -- 創建游標，用於遍歷每一條操作
  DECLARE @iCursor CURSOR
  DECLARE @cAction NVARCHAR(10)
  DECLARE @dAction DATETIME
  DECLARE @iMap2AutoIndex INT
  SET @dAction = GETDATE()
  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM inserted
      SET @cAction = '新增' -- INSERT
  END
  ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM inserted
      SET @cAction = '修改' -- UPDATE
  END
  ELSE IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM deleted
      SET @cAction = '刪除' -- DELETE
  END
  -- 遍歷操作
  OPEN @iCursor
  FETCH NEXT FROM @iCursor INTO @iMap2AutoIndex
  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- 紀錄數據
    DECLARE @cOldData NVARCHAR(max)
    DECLARE @cNewData NVARCHAR(max)
    IF @cAction = '新增'
    BEGIN
        SET @cOldData = NULL
        SET @cNewData = (SELECT * FROM inserted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
    END
    ELSE IF @cAction = '修改'
    BEGIN
        SET @cOldData = (SELECT * FROM deleted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
        SET @cNewData = (SELECT * FROM inserted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
    END
    IF @cAction = '刪除'
    BEGIN
        SET @cOldData = (SELECT * FROM deleted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
        SET @cNewData = NULL
    END
    -- 插入數據
    INSERT INTO zTable_Log
    VALUES (@cAction, @dAction, @iMap2AutoIndex, @cOldData, @cNewData)

    FETCH NEXT FROM @iCursor INTO @iMap2AutoIndex
  END

  CLOSE @iCursor
  DEALLOCATE @iCursor
END
GO

這個SQL指令執行完成後，沒有意外的話就可以在SSMS的管理介面中，在zTable下面的觸發程序，找到名為zTable自動觸發紀錄這個trigger。

執行後的效果

透過zTable_Log資料表中的iAutoIndex看圖說故事：

編號1 新增王小明
編號2 新增李小華
編號3 新增陳大英
編號4. 將王小明修改成王小美
編號5、6 將名字出現小的人，日期都改成2022-12-31
編號7 將李小華改名為陳小華
編號8 將陳大英的註解改成空字串
編號9 將陳大英的註解改成NULL
編號10、11 將名字出現陳的人全部刪除

由上面的效果可以得知，無論是新增、修改、刪除所有的動作以及欄位值，都有完整的被記錄下來，日後可以用來稽查用途。但是特別要注意的是，因為所有的歷程都被保留下來，所以增刪資料的速度效能可能會大幅地降低，例如：單次要砍一萬筆資料，請想想背後資料庫會多忙碌了。

補充：增強執行期的訊息

可以透過SQL提供的一些執行期參數，來增強CRUD寫入當下的輔助資訊，例如：cHost執行SQL當下的主機名稱、cHostIP執行SQL當下的IP位址、cUser執行SQL當下的連線帳號等。若有需要的話請使用下列的指令

建立監控紀錄的LOG資料表（增強版）

CREATE TABLE [dbo].[zTable_Log]
(
  [iAutoIndex] [int] IDENTITY(1,1) NOT NULL,
  [cAction] [nvarchar](10) NOT NULL,
  [dAction] [datetime] NOT NULL,
  [iMap2AutoIndex] [int] NOT NULL,
  [cOldData] [nvarchar](max) NULL,
  [cNewData] [nvarchar](max) NULL,
  [cHost] [nvarchar](50) NULL,
  [cHostIP] [nvarchar](50) NULL,
  [cUser] [nvarchar](50) NULL,
  PRIMARY KEY CLUSTERED 
  (
	  [iAutoIndex] ASC
  ) WITH
  (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  )	ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

設計資料表觸發程序（增強版）

-- 建立資料表觸發程序
CREATE
  TRIGGER zTable自動觸發紀錄
  ON      zTable
  AFTER   INSERT, UPDATE, DELETE
AS
BEGIN
  -- 創建游標，用於遍歷每一條操作
  DECLARE @iCursor CURSOR
  DECLARE @cAction NVARCHAR(10)
  DECLARE @dAction DATETIME
  DECLARE @iMap2AutoIndex INT
  DECLARE @cHost nvarchar(50)
  DECLARE @cHostIP nvarchar(50)
  DECLARE @cUser nvarchar(50)

  SET @dAction = GETDATE()
  SET @cHost = CONVERT(NVARCHAR(50), HOST_NAME())
  SET @cHostIP = CONVERT(NVARCHAR(50), CONNECTIONPROPERTY('client_net_address'))
  SET @cUser = CONVERT(NVARCHAR(50), SYSTEM_USER)

  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM inserted
      SET @cAction = '新增' -- INSERT
  END
  ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM inserted
      SET @cAction = '修改' -- UPDATE
  END
  ELSE IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
  BEGIN
      SET @iCursor = CURSOR FOR SELECT [iAutoIndex] FROM deleted
      SET @cAction = '刪除' -- DELETE
  END
  -- 遍歷操作
  OPEN @iCursor
  FETCH NEXT FROM @iCursor INTO @iMap2AutoIndex
  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- 紀錄數據
    DECLARE @cOldData NVARCHAR(max)
    DECLARE @cNewData NVARCHAR(max)
    IF @cAction = '新增'
    BEGIN
        SET @cOldData = NULL
        SET @cNewData = (SELECT * FROM inserted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
    END
    ELSE IF @cAction = '修改'
    BEGIN
        SET @cOldData = (SELECT * FROM deleted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
        SET @cNewData = (SELECT * FROM inserted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
    END
    IF @cAction = '刪除'
    BEGIN
        SET @cOldData = (SELECT * FROM deleted WHERE [iAutoIndex] = @iMap2AutoIndex FOR XML RAW)
        SET @cNewData = NULL
    END
    -- 插入數據
    INSERT INTO zTable_Log
    VALUES (@cAction, @dAction, @iMap2AutoIndex, @cOldData, @cNewData, @cHost, @cHostIP, @cUser)

    FETCH NEXT FROM @iCursor INTO @iMap2AutoIndex
  END

  CLOSE @iCursor
  DEALLOCATE @iCursor
END
GO