MSSQL Trigger inserted deleted columns_updated Audit Log 資料異動紀錄

在MSSQL資料庫端實現資料異動記錄

陳乾正 2016/09/04 18:16:01
6928







主題：

[資料庫]在MSSQL資料庫端實現資料異動記錄

文章簡介：

描述如何在資料庫端自動攔截比對資料增刪修異動，並做統一記錄，做為異動歷程留存或日後稽核用途。

作者：

陳乾正

版本/產出日期：

V1.0 / 2016.09.04


1. 前言


在陸續建置系統過程中，我們都會碰到因客戶所屬產業法規，或者系統性質資料的特殊性，必須做到留存系統資料異動歷程軌跡的需求，一般大都會選擇在資料邏輯層撰寫資料比對記錄的相關處理邏輯，因為貼近前端知道線上登入人員資訊，二來知道系統什麼情況會造成資料異動，順便記錄資料變更紀錄，但缺點是必須逐一為每個資料邏輯層方法撰寫資料比對處理邏輯，處理運算的時間花費在應用程式伺服器，以及若有不是透過該系統異動資料的行為，例如外部系統或批次，則會有資料異動歷程不完整的風險，除非記得且要求按照相同邏輯實作記錄資料異動。

因此，是否有一個方案可在資料庫端自動完成資料異動紀錄，讓前端專注在商業邏輯處理，我們續續看下去。


2. 目的

• 瞭解MSSQL Trigger、inserted/deleted系統資料表及columns_updated()函式用法。

• 瞭解在資料庫端實現自動完成資料異動紀錄的方案做法及限制。


3. 開始前準備

• MSSQL 2008以上版本。

• 使用SQL Server Management Studio工具。


4. 方案架構


5. 方案建置說明

(1) 建立資料異動記錄所需資料表，建立指令腳本參考附錄。

TableIndex：資料表索引


欄位名稱

欄位說明

型態

備註

PK

TableId

資料表識別碼

int

自動序號


TableName

資料表名稱

varchar(100)



ColumnIndex：欄位索引


欄位名稱

欄位說明

型態

備註

PK

ColumnId

欄位識別碼

int

自動序號


ColumnName

欄位名稱

varchar(100)



DataAuditLog：資料異動紀錄


欄位名稱

欄位說明

型態

備註

PK

Id

異動紀錄識別碼

bigint

自動序號


Kind

異動種類

char(1)

I:新增 U:修改 D:刪除


TableId

資料表識別碼

int



ColumnId

欄位識別碼

int



RowId

來源紀錄識別碼

uniqueidentifier

GUID


OldValue

修改前資料

nvarchar(4000)

若為新增則Null


NewValue

修改後資料

nvarchar(4000)

若為刪除則Null


LogTime

紀錄時間

datetime



(2) 為欲進行資料異動記錄的每個商業資料資料表新增RowId欄位及值，做為異動記錄資料對應關連使用。

欄位名稱

欄位說明

型態

備註


RowId

紀錄識別碼

uniqueidentifier

GUID


(3) 為欲進行資料異動記錄的每個商業資料資料表建立Trigger，Trigger指令腳本參考附錄，資料表數量多時可撰寫簡易程式碼替換Trigger Name及附屬的Table Name。


(4) 完成。


6. 處理邏輯

(1) 資料表資料增刪修時觸發Trigger執行。

(2) Trigger維護觸發對象的資料表及欄位索引資料。

(3) Trigger依columns_updated()函式回傳值判斷那些欄位值變更，逐一處理寫入異動紀錄。


7. 測試

(1) 建立一資料表MyTable，格式如下，並附掛Trigger觸發程序。

(2) 執行資料寫入指令及異動紀錄結果。



(3) 執行資料異動指令及異動紀錄結果。



8. 限制及注意建議事項

(1) 此方案只支援以下資料型態欄位記錄。

36 uniqueidentifier

40 date

41 time

42 datetime2

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

104 bit

106 decimal

108 numeric

122 smallmoney

127 bigint

167 varchar

175 char

231 nvarchar

239 nchar

241 xml

(2) 此方案不支援以下資料型態欄位記錄。

34 image

35 text

43 datetimeoffset

98 sql_variant

99 ntext

165 varbinary

173 binary

189 timestamp

(3) 欲進行資料異動的商業資料資料表中不可包含text、ntext、image型態欄位。

在MSSQL觸發程序中，如果相容性層級設為70，SQL Server不允許參考inserted和deleted資料表中之text、ntext或image資料行。

在未來的Microsoft SQL Server版本中，將移除ntext、text和image等資料類型，應避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用nvarchar(max)、varchar(max)和varbinary(max)。AFTER和INSTEAD OF觸發程序都支援inserted或deleted資料表中的varchar(MAX)、nvarchar(MAX)和varbinary(MAX)資料。

參考https://msdn.microsoft.com/zh-tw/library/ms189799.aspx

(4) 此方案會將欄位異動資料轉型寫入nvarchar欄位，記錄資料最大長度4000字元。

(5) 此方案Trigger程式自動維護資料表索引及欄位索引資料做為關連使用，以節省異動紀錄儲存空間；亦可刪減此二個資料表直接使用MSSQL資料庫中object id值，但須注意日後資料庫搬遷重建object id值會不一致。

(6) 此方案固定以RowId識別關連來源資料紀錄與異動記錄關係，故來源資料資料表必須存在RowId欄位，以加快處理效率及日後資料串連使用便利性，RowId在商業資料資料表可為鍵值或一般欄位；亦可自行修改記錄資料實際鍵值。

(7) 此方案已考量一次多筆更新情況。

(8) 商業資料資料表中建議包含建立人員/時間、修改人員/時間欄位，資料異動時一併留存異動人員資料。

(9) 異動紀錄需要大量儲存空間建議獨立單一資料庫檔案或不同儲存體分區。

(10) 僅會新增不會修改刪除資料或系統資料表建議不記錄異動紀錄。

(11) 因效能考量，不建議使用在會大量更新的資料表，例如一次批次更新幾千或上萬筆紀錄。

(12) 增加Trigger觸發處理程序對資料異動效能上一定有些許影響，但差別僅在把效能影響放在應用程式或資料庫端，仍需依實際情況評估使用。


9. 參考來源

(1) COLUMNS_UPDATED() for audit triggers (Blog: Piotr Rodak)

http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/04/28/columns-updated.aspx

(2) T-SQL: Audit Data Changes (Blogger: _RichardABrown)

http://richbrownesq-sqlserver.blogspot.tw/2011/12/t-sql-audit-data-changes.html

(3) COLUMNS_UPDATED原理 (CSDN博客: pengxuan)

http://m.blog.csdn.net/article/details?id=48682575

(4) COLUMNS_UPDATED (Transact-SQL) (Microsoft MSDN)

https://msdn.microsoft.com/zh-tw/library/ms186329.aspx


10. 附錄

(1) 異動紀錄資料表建立腳本

create table DataAuditLog

(

Id bigint identity(1,1) not null,

Kind char(1), -- I:Insert U:Update D:Delete

TableId int,

ColumnId int,

RowId uniqueidentifier,

OldValue nvarchar(4000), -- 若為Insert則為null，最大資料長度4000字元

NewValue nvarchar(4000), -- 若為Delete則為null，最大資料長度4000字元

LogTime datetime,

constraint pk_DataAuditLog primary key (Id)

);


create index DataAuditLog_idx1

on DataAuditLog(TableId, ColumnId, RowId);



create table TableIndex

(

TableId int identity(1,1) not null,

TableName varchar(100),

constraint pk_TableIndex primary key (TableId)

);


create index TableIndex_idx1

on TableIndex(TableName);



create table ColumnIndex

(

ColumnId int identity(1,1) not null,

ColumnName varchar(100),

constraint pk_ColumnIndex primary key (ColumnId)

);


create index ColumnIndex_idx1

on ColumnIndex(ColumnName);


(2) 異動記錄Trigger觸發程序範例指令腳本

create trigger [trigger_MyTable_DataAuditLog]

on [MyTable] for insert, update, delete

as


set nocount on;


declare @DbTableId int;

declare @TableId int;

declare @TableName varchar(100);

declare @DbColumnId int;

declare @ColumnId int;

declare @ColumnName varchar(100);

declare @ColumnTypeId int;

declare @LogTime datetime;

declare @ActionType char(1);

declare @MaxColumnId int;

declare @ColumnUpdated varbinary(100);

declare @CharIndex int;

declare @BitIndex int;

declare @BitMark int;

declare @Sql varchar(2000);

declare @Sql_I varchar(200);

declare @Sql_D varchar(200);



set @DbTableId = (select parent_obj from sysobjects where id = @@procid);

set @TableName = (select name from sysobjects where id = @DbTableId);

set @LogTime = getdate();


if (select count(1) from inserted) > 0

and (select count(1) from deleted) > 0

begin

set @ActionType = 'U';

end

else

begin

if (select count(1) from inserted) > 0

begin

set @ActionType = 'I';

end

else

begin

set @ActionType = 'D';

end

end



insert into TableIndex(TableName)

select name

from sysobjects x (nolock)

where id = @DbTableId

and not exists (

select 1

from TableIndex

where TableName = x.name

);


insert into ColumnIndex(ColumnName)

select name

from sys.columns x (nolock)

where object_id = @DbTableId

and not exists (

select 1

from ColumnIndex

where ColumnName = x.name

);



set @DbColumnId = 0;

set @MaxColumnId = (select max(column_id) from sys.columns where object_id = @DbTableId);

set @ColumnUpdated = COLUMNS_UPDATED();

set @TableId = (select top 1 TableId from TableIndex where TableName = @TableName);


select * into #inserted from inserted;

select * into #deleted from deleted;


while @DbColumnId < @MaxColumnId

begin

set @DbColumnId = @DbColumnId + 1;

set @CharIndex = (@DbColumnId / 8) + 1;

set @BitIndex = (@DbColumnId % 8);

if @BitIndex = 0

begin

set @BitIndex = 8;

end

set @BitMark = power(2, @BitIndex - 1);


if @ActionType in ('I', 'D')

or substring(@ColumnUpdated, @CharIndex, 1) & @BitMark > 0

begin

/**

-- Sql Data Type Id --

36 uniqueidentifier

40 date

41 time

42 datetime2

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

104 bit

106 decimal

108 numeric

122 smallmoney

127 bigint

167 varchar

175 char

231 nvarchar

239 nchar

241 xml


-- Exclude Data Type --

34 image

35 text

43 datetimeoffset

98 sql_variant

99 ntext

165 varbinary

173 binary

189 timestamp

**/

select @ColumnName = name, @ColumnTypeId = system_type_id from sys.columns where object_id = @DbTableId and column_id = @DbColumnId;


if @ColumnTypeId not in (36, 40, 41, 42, 48, 52, 56, 58, 59, 60, 61, 62, 104, 106, 108, 122, 127, 167, 175, 231, 239, 241)

begin

continue;

end

set @ColumnId = (select top 1 ColumnId from ColumnIndex where ColumnName = @ColumnName);


if @ActionType = 'I'

begin

if @ColumnTypeId in (40, 41, 42, 58, 61)

begin

set @Sql_I = 'convert(nvarchar(4000), i.' + @ColumnName + ', 121)';

end

else

begin

if @ColumnTypeId in (59, 62)

begin

set @Sql_I = 'ltrim(str(i.' + @ColumnName + ', 100, 20))';

end

else

begin

set @Sql_I = 'convert(nvarchar(4000), i.' + @ColumnName + ')';

end

end


set @Sql = 'insert into DataAuditLog(Kind, TableId, ColumnId, RowId, OldValue, NewValue, LogTime)' +

'select ''' + @ActionType + ''', ' + convert(varchar(30), @TableId) + ', ' + convert(varchar(30), @ColumnId) +

', i.RowId, null, ' + @Sql_I + ', ''' + convert(varchar(30), @LogTime,121) + '''' +

' from #inserted i';

exec (@sql)

end


if @ActionType = 'U'

begin

if @ColumnTypeId in (40, 41, 42, 58, 61)

begin

set @Sql_I = 'convert(nvarchar(4000), i.' + @ColumnName + ', 121)';

set @Sql_D = 'convert(nvarchar(4000), d.' + @ColumnName + ', 121)';

end

else

begin

if @ColumnTypeId in (59, 62)

begin

set @Sql_I = 'ltrim(str(i.' + @ColumnName + ', 100, 20))';

set @Sql_D = 'ltrim(str(d.' + @ColumnName + ', 100, 20))';

end

else

begin

set @Sql_I = 'convert(nvarchar(4000), i.' + @ColumnName + ')';

set @Sql_D = 'convert(nvarchar(4000), d.' + @ColumnName + ')';

end

end


set @Sql = 'insert into DataAuditLog(Kind, TableId, ColumnId, RowId, OldValue, NewValue, LogTime)' +

'select ''' + @ActionType + ''', ' + convert(varchar(30), @TableId) + ', ' + convert(varchar(30), @ColumnId) +

', i.RowId, ' + @Sql_D + ', ' + @Sql_I + ', ''' + convert(varchar(30), @LogTime,121) + '''' +

' from #inserted i left join #deleted d' +

' on i.RowId = d.RowId';

exec (@sql)

end


if @ActionType = 'D'

begin

if @ColumnTypeId in (40, 41, 42, 58, 61)

begin

set @Sql_D = 'convert(nvarchar(4000), d.' + @ColumnName + ', 121)';

end

else

begin

if @ColumnTypeId in (59, 62)

begin

set @Sql_D = 'ltrim(str(d.' + @ColumnName + ', 100, 20))';

end

else

begin

set @Sql_D = 'convert(nvarchar(4000), d.' + @ColumnName + ')';

end

end


set @Sql = 'insert into DataAuditLog(Kind, TableId, ColumnId, RowId, OldValue, NewValue, LogTime)' +

'select ''' + @ActionType + ''', ' + convert(varchar(30), @TableId) + ', ' + convert(varchar(30), @ColumnId) +

', d.RowId, ' + @Sql_D + ', null, ''' + convert(varchar(30), @LogTime,121) + '''' +

' from #deleted d';

exec (@sql)

end

end

end


drop table #inserted;

drop table #deleted;


(3) 範例資料表建立腳本及測試指令碼

create table MyTable

(

RowId uniqueidentifier not null,

F001 uniqueidentifier,

F002 date,

F003 time,

F004 datetime2,

F005 tinyint,

F006 smallint,

F007 int,

F008 smalldatetime,

F009 real,

F010 money,

F011 datetime,

F012 float,

F013 bit,

F014 decimal(21,9),

F015 numeric(18,6),

F016 smallmoney,

F017 bigint,

F018 varchar(2000),

F019 char(200),

F020 nvarchar(2000),

F021 nchar(100),

F022 xml,

F023 int,

F024 int,

F103 datetimeoffset,

F104 sql_variant,

F106 varbinary(1000),

F107 binary(1000),

F108 timestamp,

CreateUser varchar(10),

CreateTime datetime,

ModifyUser varchar(10),

ModifyTime datetime,

constraint pk_MyTable primary key (RowId)

);



insert into MyTable(RowId, F001, F002, F003, F004, F005, F006, F007, F008, F009, F010, F011, F012, F013, F014, F015

, F016, F017, F018, F019, F020, F021, F022, F023, F024, CreateUser, CreateTime, ModifyUser, ModifyTime)

values(newid(), newid(), '2016/09/15', '11:22:33.345', '2016/09/16 22:33:44.456', 123, 234, 12345678, '2016/09/17 23:44:55'

, 55.123456, 3456.78, '2016/09/18 01:33:44.456', 999.123456789, 1, 899.99, 123456, 5555.55, 1234567890123

, 'aaaaaaaaaaaaaaaa', 'bbbbbbbbbbbb', 'AAAAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBB'

, '<order><orderno>201609001</orderno><orderdate>2016/09/15</orderdate></order>'

, 23, 24

, '11259', getdate(), null, null);



insert into MyTable(RowId, F001, F002, F003, F004, F005, F006, F007, F008, F009, F010, F011, F012, F013, F014, F015

, F016, F017, F018, F019, F020, F021, F022, CreateUser, CreateTime, ModifyUser, ModifyTime)

values(newid(), newid(), '2016/10/15', '01:02:03.345', '2016/10/16 02:03:04.456', 234, 345, 23456789, '2016/10/17 23:44:55'

, 56.123456, 4567.78, '2016/10/18 01:33:44.456', 9999.123456789, 0, 8999.99, 234567, 6666.55, 2345678901234

, 'abbbbbbbbbbbbbbb', 'bccccccccccc', 'ABBBBBBBBBBBBBBBBB', 'BCCCCCCCCCCCC'

, '<order><orderno>201609002</orderno><orderdate>2016/10/15</orderdate></order>'

, '11788', getdate(), null, null);



insert into MyTable(RowId, F001, F002, F003, F004, F005, F006, F007, F008, F009, F010, F011, F012, F013, F014, F015

, F016, F017, F018, F019, F020, F021, F022, CreateUser, CreateTime, ModifyUser, ModifyTime)

values(newid(), newid(), '2016/11/15', '02:03:04.345', '2016/11/16 03:04:05.456', 235, 456, 34567890, '2016/12/17 23:44:55'

, 67.123456, 5678.78, '2016/11/18 02:33:44.456', 9998.123456789, 0, 8998.99, 345678, 7777.55, 3456789012345

, 'abcccccccccccccc', 'bcdddddddddd', 'ABCCCCCCCCCCCCCCCC', 'BCDDDDDDDDDDD'

, '<order><orderno>201609003</orderno><orderdate>2016/11/15</orderdate></order>'

, '11449', getdate(), null, null);



update MyTable

set F001 = newid()

, F002 = '2016/10/16'

, F003 = '01:02:03.999'

, F004 = '2016/10/16 02:03:04.999'

, F005 = 188

, F006 = 399

, F007 = 23456799

, F008 = '2016/10/17 23:44:59'

, F009 = 56.123499

, F010 = 4567.99

, F011 = '2016/10/18 01:33:44.999'

, F012 = 9999.123456799

, F013 = 1

, F014 = 8999.88

, F015 = 234599

, F016 = 6666.99

, F017 = 2345678909999

, F018 = 'abbbbbbbbbbbbb99'

, F019 = 'bccccccccc99'

, F020 = 'ABBBBBBBBBBBBBBB99'

, F021 = 'BCCCCCCCCCC99'

, F022 = '<order><orderno>201609002</orderno><orderdate>2016/10/25</orderdate></order>'

, ModifyUser = '11578'

, ModifyTime = getdate()

where RowId = '78F8F254-934A-4223-86FA-5B96BD5B8AB9';



update MyTable

set F003 = '02:03:04.888'

, F004 = '2016/11/16 03:04:05.888'

, F013 = 1

, F014 = 8999.88

, F018 = 'abcccccccccccc88'

, F019 = 'bcdddddddd88'

, F020 = 'ABCCCCCCCCCCCCCC88'

, ModifyUser = '11668'

, ModifyTime = getdate()

where RowId = '7446241A-C6B3-4E54-A792-7AEB220C0949';



update MyTable

set F103 = '2016/09/11 15:32:55.456+08:00'

, F104 = 'ABC123'

, F106 = convert(varbinary(1000),'abcccccccccccc88')

, F107 = convert(varbinary(1000),'bcdddddddd88')

where RowId = '78F8F254-934A-4223-86FA-5B96BD5B8AB9';



delete MyTable

where RowId = '78F8F254-934A-4223-86FA-5B96BD5B8AB9';



陳乾正
相關文章
Connect Cloud SQL from Google Kubernetes Engine
Schedule Cloud SQL backups
Loading CSV data into BigQuery
Export Cloud SQL date using CSV files
使用 Ora2pg 將 Oracle 遷移至 PostgreSQL
關聯式資料庫讀寫分離及分表分庫
最新文章
Spring AI
【digiLogs 勇者指南】03.節點主控台監控應用
【digiLogs 勇者指南】07.建立客製化報表
【digiLogs 勇者指南】05.Log查詢與應用
【digiLogs 勇者指南】09.機器學習模型應用理論
【digiLogs 勇者指南】01.介面之奧義