在我的应用程序的 SQL Server 后端中,我想为我的一些关键表创建历史记录表,以跟踪行的更改历史。
我的整个应用程序使用存储过程,没有嵌入式 SQL。 修改这些表的唯一数据库连接将通过应用程序和存储过程接口进行。 传统上,我合作过的商店使用触发器来执行此任务。
如果我可以选择使用存储过程或触发器,哪个更好?哪个更快?
在我的应用程序的 SQL Server 后端中,我想为我的一些关键表创建历史记录表,以跟踪行的更改历史。
我的整个应用程序使用存储过程,没有嵌入式 SQL。 修改这些表的唯一数据库连接将通过应用程序和存储过程接口进行。 传统上,我合作过的商店使用触发器来执行此任务。
如果我可以选择使用存储过程或触发器,哪个更好?哪个更快?
触发器。
我们编写了一个GUI(内部称为Red Matrix Reloaded),以便轻松创建/管理审计日志触发器。
这是一些使用的DDL:
CREATE TABLE [AuditLog] (
[AuditLogID] [int] IDENTITY (1, 1) NOT NULL ,
[ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()),
[RowGUID] [uniqueidentifier] NOT NULL ,
[ChangeType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TableName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Hostname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserGUID] [uniqueidentifier] NULL ,
[TagGUID] [uniqueidentifier] NULL ,
[Tag] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
CREATE TRIGGER LogInsert_Nodes ON dbo.Nodes
FOR INSERT
AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/*We dont' log individual field changes Old/New because the row is new.
So we only have one record - INSERTED*/
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'INSERTED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
null, --OldValue
null --NewValue
FROM Inserted i
CREATE TRIGGER LogUpdate_Nodes ON dbo.Nodes
FOR UPDATE AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/* ParentNodeGUID uniqueidentifier */
IF UPDATE (ParentNodeGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ParentNodeGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.ParentNodeGUID, --OldValue
i.ParentNodeGUID --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ParentNodeGUID IS NULL AND i.ParentNodeGUID IS NOT NULL)
OR (d.ParentNodeGUID IS NOT NULL AND i.ParentNodeGUID IS NULL)
OR (d.ParentNodeGUID <> i.ParentNodeGUID)
END
/* Caption varchar(255) */
IF UPDATE (Caption)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'Caption', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
d.Caption, --OldValue
i.Caption --NewValue
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.Caption IS NULL AND i.Caption IS NOT NULL)
OR (d.Caption IS NOT NULL AND i.Caption IS NULL)
OR (d.Caption <> i.Caption)
END
...
/* ImageGUID uniqueidentifier */
IF UPDATE (ImageGUID)
BEGIN
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue, NewValue)
SELECT
getdate(), --ChangeDate
i.NodeGUID, --RowGUID
'UPDATED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'ImageGUID', --FieldName
i.ParentNodeGUID, --TagGUID
i.Caption, --Tag
(SELECT Caption FROM Nodes WHERE NodeGUID = d.ImageGUID), --OldValue
(SELECT Caption FROM Nodes WHERE NodeGUID = i.ImageGUID) --New Value
FROM Inserted i
INNER JOIN Deleted d
ON i.NodeGUID = d.NodeGUID
WHERE (d.ImageGUID IS NULL AND i.ImageGUID IS NOT NULL)
OR (d.ImageGUID IS NOT NULL AND i.ImageGUID IS NULL)
OR (d.ImageGUID <> i.ImageGUID)
END
CREATE TRIGGER LogDelete_Nodes ON dbo.Nodes
FOR DELETE
AS
/* Load the saved context info UserGUID */
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
DECLARE @NullGUID uniqueidentifier
SELECT @NullGUID = '{00000000-0000-0000-0000-000000000000}'
IF @SavedUserGUID = @NullGUID
BEGIN
SET @SavedUserGUID = NULL
END
/*We dont' log individual field changes Old/New because the row is new.
So we only have one record - DELETED*/
INSERT INTO AuditLog(
ChangeDate, RowGUID, ChangeType,
Username, HostName, AppName,
UserGUID,
TableName, FieldName,
TagGUID, Tag,
OldValue,NewValue)
SELECT
getdate(), --ChangeDate
d.NodeGUID, --RowGUID
'DELETED', --ChangeType
USER_NAME(), HOST_NAME(), APP_NAME(),
@SavedUserGUID, --UserGUID
'Nodes', --TableName
'', --FieldName
d.ParentNodeGUID, --TagGUID
d.Caption, --Tag
null, --OldValue
null --NewValue
FROM Deleted d
为了知道软件中是哪个用户进行了更新,在每次连接时通过调用一个存储过程来“向 SQL Server 登录”,使其自身被记录日志。
CREATE PROCEDURE dbo.SaveContextUserGUID @UserGUID uniqueidentifier AS
/* Saves the given UserGUID as the session's "Context Information" */
IF @UserGUID IS NULL
BEGIN
PRINT 'Emptying CONTEXT_INFO because of null @UserGUID'
DECLARE @BinVar varbinary(128)
SET @BinVar = CAST( REPLICATE( 0x00, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar
RETURN 0
END
DECLARE @UserGUIDBinary binary(16) --a guid is 16 bytes
SELECT @UserGUIDBinary = CAST(@UserGUID as binary(16))
SET CONTEXT_INFO @UserGUIDBinary
/* To load the guid back
DECLARE @SavedUserGUID uniqueidentifier
SELECT @SavedUserGUID = CAST(context_info as uniqueidentifier)
FROM master.dbo.sysprocesses
WHERE spid = @@SPID
select @SavedUserGUID AS UserGUID
*/
注意事项
有时,“OldValue”和“NewValue”值会被写成子查询,以获得有意义的字符串。例如:
OldValue:{233d-ad34234...}
NewValue:{883-sdf34...}
与之相比,这种写法在审核跟踪中不太有用:
OldValue: Daimler Chrysler
NewValue: Cerberus Capital Management
最后的提示:如果你不想像我们一样做,那没问题,每个人都可以自由选择。
context_info
中记录网站用户。 - Ian Boyd我们使用第三方工具ApexSQL Audit来生成触发器。
以下是触发器的背景和数据存储方式。希望大家能够发现这对反向工程过程有用。与Ian Boyd示例中显示的方式有些不同,因为它允许每个列分别进行审计。
表1 - 保存事务详情(谁、何时、应用程序、主机名等)
CREATE TABLE [dbo].[AUDIT_LOG_TRANSACTIONS](
[AUDIT_LOG_TRANSACTION_ID] [int] IDENTITY(1,1) NOT NULL,
[DATABASE] [nvarchar](128) NOT NULL,
[TABLE_NAME] [nvarchar](261) NOT NULL,
[TABLE_SCHEMA] [nvarchar](261) NOT NULL,
[AUDIT_ACTION_ID] [tinyint] NOT NULL,
[HOST_NAME] [varchar](128) NOT NULL,
[APP_NAME] [varchar](128) NOT NULL,
[MODIFIED_BY] [varchar](128) NOT NULL,
[MODIFIED_DATE] [datetime] NOT NULL,
[AFFECTED_ROWS] [int] NOT NULL,
[SYSOBJ_ID] AS (object_id([TABLE_NAME])),
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_TRANSACTION_ID] ASC
)
)
表2 - 保存前/后的值。
CREATE TABLE [dbo].[AUDIT_LOG_DATA](
[AUDIT_LOG_DATA_ID] [int] IDENTITY(1,1) NOT NULL,
[AUDIT_LOG_TRANSACTION_ID] [int] NOT NULL,
[PRIMARY_KEY_DATA] [nvarchar](1500) NOT NULL,
[COL_NAME] [nvarchar](128) NOT NULL,
[OLD_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_LONG] [ntext] NULL,
[NEW_VALUE_BLOB] [image] NULL,
[NEW_VALUE] AS (isnull(CONVERT([varchar](8000), [NEW_VALUE_LONG],0),CONVERT([varchar](8000),CONVERT([varbinary](8000),substring([NEW_VALUE_BLOB],(1),(8000)),0),0))),
[OLD_VALUE] AS (CONVERT([varchar](8000),[OLD_VALUE_LONG],0)),
[PRIMARY_KEY] AS ([PRIMARY_KEY_DATA]),
[DATA_TYPE] [char](1) NOT NULL,
[KEY1] [nvarchar](500) NULL,
[KEY2] [nvarchar](500) NULL,
[KEY3] [nvarchar](500) NULL,
[KEY4] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED
(
[AUDIT_LOG_DATA_ID] ASC
)
)
插入触发器
我不会展示更新触发器,因为它们比较长,并且具有与此相同的逻辑。
CREATE TRIGGER [dbo].[tr_i_AUDIT_Audited_Table]
ON [dbo].[Audited_Table]
FOR INSERT
NOT FOR REPLICATION
As
BEGIN
DECLARE
@IDENTITY_SAVE varchar(50),
@AUDIT_LOG_TRANSACTION_ID Int,
@PRIM_KEY nvarchar(4000),
@ROWS_COUNT int
SET NOCOUNT ON
Select @ROWS_COUNT=count(*) from inserted
Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))
INSERT
INTO dbo.AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME,
TABLE_SCHEMA,
AUDIT_ACTION_ID,
HOST_NAME,
APP_NAME,
MODIFIED_BY,
MODIFIED_DATE,
AFFECTED_ROWS,
[DATABASE]
)
values(
'Audited_Table',
'dbo',
2, -- ACTION ID For INSERT
CASE
WHEN LEN(HOST_NAME()) < 1 THEN ' '
ELSE HOST_NAME()
END,
CASE
WHEN LEN(APP_NAME()) < 1 THEN ' '
ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
@ROWS_COUNT,
'Database_Name'
)
Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()
--This INSERT INTO code is repeated for each columns that is audited.
--Below are examples for only two columns
INSERT INTO dbo.AUDIT_LOG_DATA
(
AUDIT_LOG_TRANSACTION_ID,
PRIMARY_KEY_DATA,
COL_NAME,
NEW_VALUE_LONG,
DATA_TYPE
, KEY1
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
'Column1',
CONVERT(nvarchar(4000), NEW.[Column1], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column1] Is Not Null
--value is inserted for each column that is selected for auditin
INSERT INTO dbo.AUDIT_LOG_DATA
(
AUDIT_LOG_TRANSACTION_ID,
PRIMARY_KEY_DATA,
COL_NAME,
NEW_VALUE_LONG,
DATA_TYPE
, KEY1
)
SELECT
@AUDIT_LOG_TRANSACTION_ID,
convert(nvarchar(1500), IsNull('[PK_Column]='+CONVERT(nvarchar(4000), NEW.[PK_Column], 0), '[PK_Column] Is Null')),
'Column2',
CONVERT(nvarchar(4000), NEW.[Column2], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[PK_Column], 0))
FROM inserted NEW
WHERE NEW.[Column2] Is Not Null
End
免责声明:我与Apex没有任何关联,但我在目前的工作中使用他们的工具。
建议的方法取决于您的要求。如果历史记录表是用于审计跟踪,则需要捕获每个操作。如果历史记录表仅出于性能原因,则应该足以使用定期的SQL代理数据传输作业。
要捕获每个操作,请使用AFTER TRIGGER或Change Data Capture。
AFTER TRIGGER为您提供了两个临时表,在触发器内进行操作:
您可以从这些临时表向历史记录表执行插入操作,您的历史记录表将始终是最新的。您可能希望在历史记录表中添加版本编号、时间戳或两者都用于区分对单个源行的更改。
Change Data Capture(CDC)旨在创建一个增量表,您可以将其用作加载数据到数据仓库(或历史记录表)的源。与触发器不同,CDC是异步的,您可以使用任何方法和调度来填充目标(sprocs、SSIS)。
使用CDC可以访问原始数据和更改的数据。变更跟踪(CT)仅检测更改的行。使用CDC可以构建完整的审计跟踪,但使用CT则不行。CDC和CT仅适用于MSSQL 2008企业版和开发人员版。
需要非常注意的一个问题是要确定这张表的预期用途,并确保它是合适的。
具体来说,如果它是为利益相关者而设置的操作审计跟踪表,那么与记录更改前后快照的表是截然不同的。 (事实上,我很难想象记录更改的好用途,除了调试之外。)
审计跟踪通常至少需要用户ID,时间戳和操作代码 - 可能还需要一些有关操作的详细信息。例如 - 更改采购订单上一行项目的订购数量。
对于这种类型的审计跟踪,您不应该使用触发器。嵌入这些事件的生成的BR层越高,效果就越好。
另一方面,对于记录级别的更改,触发器是正确的匹配。但也经常可以从您的dbms日志文件中轻松获取此信息。
我更喜欢使用触发器来进行审计表,因为触发器可以捕获所有的更新、插入和删除操作,而不仅仅是通过某些存储过程调用的更新、插入和删除操作:
CREATE TRIGGER [dbo].[tr_Employee_rev]
ON [dbo].[Employee]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
IF EXISTS(SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
BEGIN
INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'u', GetDate(), SYSTEM_USER FROM INSERTED
END
IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT inserted.ID, inserted.Firstname,inserted.Initial,inserted.Surname,inserted.Birthdate,'i', GetDate(), SYSTEM_USER FROM INSERTED
END
IF EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
BEGIN
INSERT INTO [EmployeeRev](EmployeeID,Firstname,Initial,Surname,Birthdate,operation, updated, updatedby) SELECT deleted.ID, deleted.Firstname,deleted.Initial,deleted.Surname,deleted.Birthdate,'d', GetDate(), SYSTEM_USER FROM DELETED
END
END
我使用SQLServer来生成修订表的SQL,而不是手动编码。这段代码可在https://github.com/newdigate/sqlserver-revision-tables上找到。
触发器。这是我的方法:
以这种方式进行审计可以保持源表中的当前状态,并将所有历史记录保存在审计表中,并通过关键列轻松识别。
触发器。现在你可能会说数据更新的唯一方式是通过存储过程,但事情可能会改变,或者你可能需要进行大规模的插入/更新,使用存储过程将会太麻烦。选择触发器。