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