我需要在SQL Server 2005数据库中实现两个表的更改跟踪，需要审计添加、删除、更新(并详细说明更新了什么)。我计划使用触发器来完成此操作，但看起来这很容易出错。
有人能发布一个成功且优雅地完成这项任务的更新触发器示例吗？ 我希望最终得到具有以下结构的审计表：
- ID
- LogDate
- TableName
- TransactionType (update/insert/delete)
- RecordID
- FieldName
- OldValue
- NewValue
...你有何想法?
在SQL Server中创建审计触发器
34
- Mike Cole
2
请点击以下链接：http://stackoverflow.com/questions/1906753/sql-statement-from-dml-trigger - Paresh
1我找到了一个存储过程，可以生成插入、更新、删除触发器和审计表。http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL - FarFigNewton
10个回答
38
我想强调几点：
使用代码生成器 你不能有一个单一的过程来跟踪所有表，你需要在每个被跟踪的表上生成类似但不同的触发器。这种工作最适合自动化代码生成。如果是我的话，我会使用XSLT转换从XML生成代码，而XML可以根据元数据自动生成。这样你就可以通过重新生成触发器来轻松地维护它们，每次你修改审计逻辑/结构或者添加/修改目标表时。
考虑进行容量规划以应对审计。一个跟踪所有值变化的审计表将是数据库中最大的表：它将包含所有当前数据和所有当前数据的历史记录。这样的表将使数据库大小增加2-3个数量级（x10，x100）。并且审计表很快就会成为所有操作的瓶颈：
- 每个DML操作都需要在审计表中锁定
- 所有管理和维护操作都必须考虑由于审计而导致的数据库大小
考虑到模式更改。一个名为“Foo”的表可能会被删除，然后创建一个名为“Foo”的不同表。审计跟踪必须能够区分这两个不同的对象。最好使用慢速变化维度方法。
考虑需要高效地删除审计记录。当应用程序主体策略所规定的保留期到期时，你需要能够删除到期的审计记录。现在可能看起来不是很重要，但5年后，当第一条记录到期时，审计表已经增长到9.5TB，这可能会成为一个问题。
考虑需要查询审计。审计表结构必须准备好，以便对审计进行高效的查询响应。如果您的审计无法查询，则没有任何价值。这些查询将完全受您的要求驱动，只有您知道这些要求，但大多数审计记录都是根据时间间隔（“昨天7点到8点之间发生了什么变化？”），对象（“此表中的此记录发生了哪些更改？”）或作者（“Bob在数据库中进行了哪些更改？”）进行查询。- Remus Rusanu
2
1很棒的输入，帮助我更好地规划。我还想分享这个链接，它有点像你所说的：http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx - hitec
1在CodeProject上有一个SQL文件，它可以生成审计触发器和审计表。只需在您的数据库上运行存储过程并传递表名，然后插入、更新、删除触发器和审计表将被创建。http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL - FarFigNewton
19
我们正在使用ApexSQL Audit来生成审计触发器，以下是该工具使用的数据结构。如果您不打算购买第三方解决方案，可以在试用模式下安装此工具，了解它们如何实现触发器和存储，然后创建类似于自己的东西。
我没有详细介绍这些表的工作原理，但希望这将使您有所启发。
- Igor Voplov
14
按照您想要的方式，没有通用的方法来完成它。最终，您需要为每个表编写大量的代码。更不用说如果需要比较每个列的更改，可能会非常缓慢。
此外，您可能在同一时间更新多行，这意味着您需要打开游标来循环遍历所有记录。
我会使用与您正在跟踪的表结构相同的表来完成此操作，并稍后取消关联以显示实际更改的列。我还会跟踪实际进行更改的会话。这假定您在被跟踪的表中具有主键。
所以，给定这样一个表：
CREATE TABLE TestTable
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40))
我会在audit模式下创建一个像这样的审计表。
CREATE TABLE Audit.TestTable
(SessionID UNIQUEIDENTIFER NOT NULL,
ID INT NOT NULL,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40),
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),
ChangedDate DATETIME NOT NULL Default GETDATE(),
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())
同时还需要一个像这样的 Update 触发器
CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @SessionID UNIQUEIDENTIFER
SET @SessionID = NEWID()
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted
END
这个运行非常快。在报告期间，你只需要基于sessionID和主键连接行并生成报告。或者你可以有一个批处理作业，定期遍历审核表中的所有表格，并准备一个显示更改的名称-值对。
希望能对你有所帮助。
- no_one
5
请参考 http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx 获取一种通用的方法。 - Michael Freidgeim
五年后的称重...这大致是我要采取的方法，尽管不是为每个更改在审计表中创建两条记录，而是创建一条记录，列名格式为'OldName1'、'OldName2'和'NewName1'、'NewName2'。这种变化对审计表的大小不应该有任何影响，查询应该同样容易，并且由于只需要一个INSERT命令，它可能会提高性能。不知道是否存在任何明显的缺点？ - Philip Stratford
把一行数据插入到auid表中不是更好吗？原始表中的值将是当前（也称为“新”）值。从技术上讲，插入只需要记录要记录的其他数据（例如用户ID和时间戳）。对于更新和删除操作，请记录修改前的值。 - Justin
但是您实际上正在将表的数量翻倍。 - levi
3
看起来很简单，直到你的表中有图像/二进制等元素时，它才能正常工作。 您将整个旧记录和整个新记录作为xml。 对于一次批量插入多列，也应该正常工作。
CREATE TABLE _AuditTable
(Aud_Id int identity(1,1) primary key,
Aud_TableName varchar(100),
Aud_ActionType char(1),
Aud_Username varchar(100),
Aud_OLDValues xml,
Aud_NEWValues xml,
Aud_OperationDate datetime DEFAULT GETDATE()
)
并触发代码
CREATE TRIGGER _test2_InsertUpdate on _test2
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted)
RETURN;
declare @tablename varchar(100)
SELECT @tablename = OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)
/*Action*/
DECLARE @ActionType char(1)
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @ActionType = 'U'
ELSE
SELECT @ActionType = 'I'
ELSE
SELECT @ActionType = 'D'
declare @inserted xml, @deleted xml
SET @inserted = (SELECT * FROM inserted FOR XML PATH)
SET @deleted = (SELECT * FROM deleted FOR XML PATH)
INSERT INTO _AuditTable(Aud_TableName, Aud_ActionType, Aud_Username, Aud_OLDValues, Aud_NEWValues)
SELECT @tablename, @ActionType, SUSER_SNAME(), @deleted, @inserted
END
输出
Aud_Id | Aud_TableName | Aud_ActionType | Aud_Username | Aud_OLDValues | Aud_NEWValues | Aud_OperationDate
1 |_test2 | I |abc\mR | NULL |<row><name>abc</name></row> | 2018-11-07 12:38:34.937
- mr R
1
每个要监视的表都需要自己的触发器。很明显，正如接受的答案所指出的那样，代码生成是一件好事情。
如果您喜欢这种方法，可能会想到使用此触发器，并为每个表单独替换一些通用步骤的生成代码。
尽管如此，我创建了一个完全通用的审计触发器。被观察的表必须有一个主键，但这个主键甚至可以是多列的。
某些列类型（如BLOB）可能无法正常工作，但您可以轻松地将它们排除在外。
这不会是最佳性能:-D
老实说：这更像是一种练习...
--一些测试数据
--这是当前的内容
--现在让我们通过一些操作来测试它：
--检查最终状态
清理（小心真实数据！）
插入的结果。
如果您喜欢这种方法，可能会想到使用此触发器，并为每个表单独替换一些通用步骤的生成代码。
尽管如此，我创建了一个完全通用的审计触发器。被观察的表必须有一个主键，但这个主键甚至可以是多列的。
某些列类型（如BLOB）可能无法正常工作，但您可以轻松地将它们排除在外。
这不会是最佳性能:-D
老实说：这更像是一种练习...
SET NOCOUNT ON;
GO
CREATE TABLE AuditTest(ID UNIQUEIDENTIFIER
,LogDate DATETIME
,TableSchema VARCHAR(250)
,TableName VARCHAR(250)
,AuditType VARCHAR(250),Content XML);
GO
--一些表用于测试（故意使用奇怪的主键列...）
CREATE TABLE dbo.Testx(ID1 DATETIME NOT NULL
,ID2 UNIQUEIDENTIFIER NOT NULL
,Test1 VARCHAR(100)
,Test2 DATETIME);
--Add a two column PK
ALTER TABLE dbo.Testx ADD CONSTRAINT PK_Test PRIMARY KEY(ID1,ID2);
--一些测试数据
INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
({d'2000-01-01'},NEWID(),'Test1',NULL)
,({d'2000-02-01'},NEWID(),'Test2',{d'2002-02-02'});
--这是当前的内容
SELECT * FROM dbo.Testx;
GO
--审计的触发器
CREATE TRIGGER [dbo].[UpdateTestTrigger]
ON [dbo].[Testx]
FOR UPDATE,INSERT,DELETE
AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) RETURN;
SET NOCOUNT ON;
DECLARE @tableSchema VARCHAR(250);
DECLARE @tableName VARCHAR(250);
DECLARE @AuditID UNIQUEIDENTIFIER=NEWID();
DECLARE @LogDate DATETIME=GETDATE();
SELECT @tableSchema = sch.name
,@tableName = tb.name
FROM sys.triggers AS tr
INNER JOIN sys.tables AS tb ON tr.parent_id=tb.object_id
INNER JOIN sys.schemas AS sch ON tb.schema_id=sch.schema_id
WHERE tr.object_id = @@PROCID
DECLARE @tp VARCHAR(10)=CASE WHEN EXISTS(SELECT 1 FROM deleted) AND EXISTS(SELECT 1 FROM inserted) THEN 'upd'
ELSE CASE WHEN EXISTS(SELECT 1 FROM deleted) AND NOT EXISTS(SELECT 1 FROM inserted) THEN 'del' ELSE 'ins' END END;
SELECT * INTO #tmpInserted FROM inserted;
SELECT * INTO #tmpDeleted FROM deleted;
SELECT kc.ORDINAL_POSITION, kc.COLUMN_NAME
INTO #tmpPKColumns
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON tc.TABLE_CATALOG=kc.TABLE_CATALOG
AND tc.TABLE_SCHEMA=kc.TABLE_SCHEMA
AND tc.TABLE_NAME=kc.TABLE_NAME
AND tc.CONSTRAINT_NAME=kc.CONSTRAINT_NAME
AND tc.CONSTRAINT_TYPE='PRIMARY KEY'
WHERE tc.TABLE_SCHEMA=@tableSchema
AND tc.TABLE_NAME=@tableName
ORDER BY kc.ORDINAL_POSITION;
DECLARE @pkCols VARCHAR(MAX)=
STUFF
(
(
SELECT 'UNION ALL SELECT ''' + pc.COLUMN_NAME + ''' AS [@name] , CAST(COALESCE(i.' + QUOTENAME(pc.COLUMN_NAME) + ',d.' + QUOTENAME(pc.COLUMN_NAME) + ') AS VARCHAR(MAX)) AS [@value] '
FROM #tmpPKColumns AS pc
ORDER BY pc.ORDINAL_POSITION
FOR XML PATH('')
),1,16,'');
DECLARE @pkColsCompare VARCHAR(MAX)=
STUFF
(
(
SELECT 'AND i.' + QUOTENAME(pc.COLUMN_NAME) + '=d.' + QUOTENAME(pc.COLUMN_NAME)
FROM #tmpPKColumns AS pc
ORDER BY pc.ORDINAL_POSITION
FOR XML PATH('')
),1,3,'');
DECLARE @cols VARCHAR(MAX)=
STUFF
(
(
SELECT ',' + CASE WHEN @tp='upd' THEN
'CASE WHEN (i.[' + COLUMN_NAME + ']!=d.[' + COLUMN_NAME + '] ' +
'OR (i.[' + COLUMN_NAME + '] IS NULL AND d.[' + COLUMN_NAME + '] IS NOT NULL) ' +
'OR (i.['+ COLUMN_NAME + '] IS NOT NULL AND d.[' + COLUMN_NAME + '] IS NULL)) ' +
'THEN ' ELSE '' END +
'(SELECT ''' + COLUMN_NAME + ''' AS [@name]' +
CASE WHEN @tp IN ('upd','del') THEN ',ISNULL(CAST(d.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@old]' ELSE '' END +
CASE WHEN @tp IN ('ins','upd') THEN ',ISNULL(CAST(i.[' + COLUMN_NAME + '] AS NVARCHAR(MAX)),N''##NULL##'') AS [@new] ' ELSE '' END +
' FOR XML PATH(''Column''),TYPE) ' + CASE WHEN @tp='upd' THEN 'END' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=@tableSchema AND TABLE_NAME=@tableName
FOR XML PATH('')
),1,1,''
);
DECLARE @cmd VARCHAR(MAX)=
'SET LANGUAGE ENGLISH;
WITH ChangedColumns AS
(
SELECT A.PK' +
',A.PK.query(''data(/PK/Column/@value)'').value(''text()[1]'',''nvarchar(max)'') AS PKVals' +
',Col.*
FROM #tmpInserted AS i
FULL OUTER JOIN #tmpDeleted AS d ON ' + @pkColsCompare +
' CROSS APPLY
(
SELECT ' + @cols + '
FOR XML PATH(''''),TYPE
) AS Col([Column])
CROSS APPLY(SELECT (SELECT tbl.* FROM (SELECT ' + @pkCols + ') AS tbl FOR XML PATH(''Column''), ROOT(''PK''),TYPE)) AS A(PK)
)
INSERT INTO AuditTest(ID,LogDate,TableSchema,TableName,AuditType,Content)
SELECT ''' + CAST(@AuditID AS VARCHAR(MAX)) + ''',''' + CONVERT(VARCHAR(MAX),@LogDate,126) + ''',''' + @tableSchema + ''',''' + @tableName + ''',''' + @tp + '''
,(
SELECT ''' + @tableSchema + ''' AS [@TableSchema]
,''' + @tableName + ''' AS [@TableName]
,''' + @tp + ''' AS [@ActionType]
,(
SELECT ChangedColumns.PK AS [*]
,(
SELECT x.[Column] AS [*],''''
FROM ChangedColumns AS x
WHERE x.PKVals=ChangedColumns.PKVals
FOR XML PATH(''Values''),TYPE
)
FROM ChangedColumns
FOR XML PATH(''Row''),TYPE
)
FOR XML PATH(''Changes'')
);';
EXEC (@cmd);
DROP TABLE #tmpInserted;
DROP TABLE #tmpDeleted;
END
GO
--现在让我们通过一些操作来测试它：
UPDATE dbo.Testx SET Test1='New 1' WHERE ID1={d'2000-01-01'};
UPDATE dbo.Testx SET Test1='New 1',Test2={d'2000-01-01'} ;
DELETE FROM dbo.Testx WHERE ID1={d'2000-02-01'};
DELETE FROM dbo.Testx WHERE ID1=GETDATE(); --no affect
INSERT INTO dbo.Testx(ID1,ID2,Test1,Test2) VALUES
({d'2000-03-01'},NEWID(),'Test3',{d'2001-03-03'})
,({d'2000-04-01'},NEWID(),'Test4',{d'2001-04-04'})
,({d'2000-05-01'},NEWID(),'Test5',{d'2001-05-05'});
UPDATE dbo.Testx SET Test2=NULL; --all rows
DELETE FROM dbo.Testx WHERE ID1 IN ({d'2000-02-01'},{d'2000-03-01'});
GO
--检查最终状态
SELECT * FROM dbo.Testx;
SELECT * FROM AuditTest;
GO
清理（小心真实数据！）
DROP TABLE dbo.Testx;
GO
DROP TABLE dbo.AuditTest;
GO
插入的结果。
<Changes TableSchema="dbo" TableName="Testx" ActionType="ins">
<Row>
<PK>
<Column name="ID1" value="May 1 2000 12:00AM" />
<Column name="ID2" value="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
</PK>
<Values>
<Column name="ID1" new="May 1 2000 12:00AM" />
<Column name="ID2" new="C2EB4D11-63F8-434E-8470-FB4A422A4ED1" />
<Column name="Test1" new="Test5" />
<Column name="Test2" new="May 5 2001 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Apr 1 2000 12:00AM" />
<Column name="ID2" value="28625CE7-9424-4FA6-AEDA-1E4853451655" />
</PK>
<Values>
<Column name="ID1" new="Apr 1 2000 12:00AM" />
<Column name="ID2" new="28625CE7-9424-4FA6-AEDA-1E4853451655" />
<Column name="Test1" new="Test4" />
<Column name="Test2" new="Apr 4 2001 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Mar 1 2000 12:00AM" />
<Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
</PK>
<Values>
<Column name="ID1" new="Mar 1 2000 12:00AM" />
<Column name="ID2" new="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
<Column name="Test1" new="Test3" />
<Column name="Test2" new="Mar 3 2001 12:00AM" />
</Values>
</Row>
</Changes>
更新的选择性结果
<Changes TableSchema="dbo" TableName="Testx" ActionType="upd">
<Row>
<PK>
<Column name="ID1" value="Feb 1 2000 12:00AM" />
<Column name="ID2" value="D7AB263A-EEFC-47DB-A6BB-A559FE8F2119" />
</PK>
<Values>
<Column name="Test1" old="Test2" new="New 1" />
<Column name="Test2" old="Feb 2 2002 12:00AM" new="Jan 1 2000 12:00AM" />
</Values>
</Row>
<Row>
<PK>
<Column name="ID1" value="Jan 1 2000 12:00AM" />
<Column name="ID2" value="318C0A66-8833-4F03-BCEF-7AB78C91704F" />
</PK>
<Values>
<Column name="Test2" old="##NULL##" new="Jan 1 2000 12:00AM" />
</Values>
</Row>
</Changes>
删除操作的结果
<Changes TableSchema="dbo" TableName="Testx" ActionType="del">
<Row>
<PK>
<Column name="ID1" value="Mar 1 2000 12:00AM" />
<Column name="ID2" value="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
</PK>
<Values>
<Column name="ID1" old="Mar 1 2000 12:00AM" />
<Column name="ID2" old="7AB56E6C-2ADC-4945-9D94-15BC9B3F270C" />
<Column name="Test1" old="Test3" />
<Column name="Test2" old="##NULL##" />
</Values>
</Row>
</Changes>
- Shnugo
1
我终于找到了一种通用的解决方案，不需要动态SQL并记录所有列的更改。
如果表发生变化，无需更改触发器。
这是审计日志：
这是一个表格的触发器：
如果表发生变化，无需更改触发器。
这是审计日志：
CREATE TABLE [dbo].[Audit](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Type] [char](1) COLLATE Latin1_General_CI_AS NULL,
[TableName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
[PK] [int] NULL,
[FieldName] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
[OldValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[NewValue] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[UpdateDate] [datetime] NULL,
[Username] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_AuditB] PRIMARY KEY CLUSTERED
(
[ID] 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]
这是一个表格的触发器：
INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
SELECT
CASE WHEN NOT EXISTS (SELECT ID FROM deleted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'I'
WHEN NOT EXISTS (SELECT ID FROM inserted WHERE ID = ISNULL(ins.PK,del.PK)) THEN 'D'
ELSE 'U' END as [Type],
'AGB' as TableName,
ISNULL(ins.PK,del.PK) as PK,
ISNULL(ins.FieldName,del.FieldName) as FieldName,
del.FieldValue as OldValue,
ins.FieldValue as NewValue,
ISNULL(ins.Username,del.Username) as Username
FROM (SELECT
insRowTbl.PK,
insRowTbl.Username,
attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
attr.insRow.value('.', 'nvarchar(max)') as FieldValue
FROM (Select
i.ID as PK,
i.LastModifiedBy as Username,
convert(xml, (select i.* for xml raw)) as insRowCol
from inserted as i
) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)
) as ins
FULL OUTER JOIN (SELECT
delRowTbl.PK,
delRowTbl.Username,
attr.delRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
attr.delRow.value('.', 'nvarchar(max)') as FieldValue
FROM (Select
d.ID as PK,
d.LastModifiedBy as Username,
convert(xml, (select d.* for xml raw)) as delRowCol
from deleted as d
) as delRowTbl
CROSS APPLY delRowTbl.delRowCol.nodes('/row/@*') as attr(delRow)
) as del
on ins.PK = del.PK and ins.FieldName = del.FieldName
WHERE
isnull(ins.FieldName,del.FieldName) not in ('LastModifiedBy', 'ID', 'TimeStamp')
and ((ins.FieldValue is null and del.FieldValue is not null)
or (ins.FieldValue is not null and del.FieldValue is null)
or (ins.FieldValue != del.FieldValue))
这个触发器是针对一个名为AGB的表而设计的。名为AGB的表有一个名为ID的主键列和一个名为LastModifiedBy的列，其中包含了最后一次编辑的用户名。
该触发器由两部分组成，首先将插入和删除表的列转换为行。这里详细说明了这一点: https://dev59.com/mWMl5IYBdhLWcg3w5aeQ#43799776
然后，它通过主键和字段名连接插入和删除表的行（每个列一行），并记录每个更改列的行。它不会记录ID、时间戳或LastModifiedByColumn的更改。
您可以插入自己的TableName、Columns名称。
您还可以创建以下存储过程，然后调用此存储过程来生成触发器:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_create_audit_trigger]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[_create_audit_trigger] AS'
END
ALTER PROCEDURE [dbo].[_create_audit_trigger]
@TableName varchar(max),
@IDColumnName varchar(max) = 'ID',
@LastModifiedByColumnName varchar(max) = 'LastModifiedBy',
@TimeStampColumnName varchar(max) = 'TimeStamp'
AS
BEGIN
PRINT 'start ' + @TableName + ' (' + @IDColumnName + ', ' + @LastModifiedByColumnName + ', ' + @TimeStampColumnName + ')'
/* if you have other audit trigger on this table and want to disable all triggers, enable this:
EXEC ('ALTER TABLE ' + @TableName + ' DISABLE TRIGGER ALL')*/
IF EXISTS (SELECT * FROM sys.objects WHERE [type] = 'TR' AND [name] = 'tr_audit_'+@TableName)
EXEC ('DROP TRIGGER [dbo].tr_audit_'+@TableName)
EXEC ('
CREATE TRIGGER [dbo].[tr_audit_'+@TableName+'] ON [ILSe].[dbo].['+@TableName+'] FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO ILSe.dbo.Audit ([Type], TableName, PK, FieldName, OldValue, NewValue, Username)
SELECT CASE WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM deleted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''I'' WHEN NOT EXISTS (SELECT '+@IDColumnName+' FROM inserted WHERE '+@IDColumnName+' = ISNULL(ins.PK,del.PK)) THEN ''D'' ELSE ''U'' END as [Type],
'''+@TableName+''' as TableName, ISNULL(ins.PK,del.PK) as PK, ISNULL(ins.FieldName,del.FieldName) as FieldName, del.FieldValue as OldValue, ins.FieldValue as NewValue, ISNULL(ins.Username,del.Username) as Username FROM
(SELECT insRowTbl.PK, insRowTbl.Username, attr.insRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.insRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select
i.'+@IDColumnName+' as PK,
i.'+@LastModifiedByColumnName+' as Username,
convert(xml, (select i.* for xml raw)) as insRowCol
from inserted as i) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes(''/row/@*'') as attr(insRow)) as ins
FULL OUTER JOIN
(SELECT delRowTbl.PK, delRowTbl.Username, attr.delRow.value(''local-name(.)'', ''nvarchar(128)'') as FieldName, attr.delRow.value(''.'', ''nvarchar(max)'') as FieldValue FROM (Select
d.'+@IDColumnName+' as PK,
d.'+@LastModifiedByColumnName+' as Username,
convert(xml, (select d.* for xml raw)) as delRowCol
from deleted as d) as delRowTbl
CROSS APPLY delRowTbl.delRowCol.nodes(''/row/@*'') as attr(delRow)) as del on ins.PK = del.PK and ins.FieldName = del.FieldName
WHERE isnull(ins.FieldName,del.FieldName) not in ('''+@LastModifiedByColumnName+''', '''+@IDColumnName+''', '''+@TimeStampColumnName+''') and
((ins.FieldValue is null and del.FieldValue is not null) or (ins.FieldValue is not null and del.FieldValue is null) or (ins.FieldValue != del.FieldValue))
END
')
PRINT 'end ' + @TableName
PRINT ''
END
- flack
0
CREATE TRIGGER TriggerName
ON TableName
FOR INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON
DECLARE @ExecStr varchar(50), @Qry nvarchar(255)
CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
)
SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO #inputbuffer
EXEC (@ExecStr)
SET @Qry = (SELECT EventInfo FROM #inputbuffer)
SELECT @Qry AS 'Query that fired the trigger',
SYSTEM_USER as LoginName,
USER AS UserName,
CURRENT_TIMESTAMP AS CurrentTime
END
- Rak
3
2Or provide any explanation? - Mike Cole
原文链接：http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm - b_levitt
不太明白下面的SQL会做什么...有人可以解释一下吗？`
DECLARE @ExecStr varchar(50), @Qry nvarchar(255) CREATE TABLE #inputbuffer
(
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(255)
) SET @ExecStr = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')' INSERT INTO #inputbuffer
EXEC (@ExecStr) SET @Qry = (SELECT EventInfo FROM #inputbuffer)
` - Mou
0
我有一个非常类似于你提出的设计的表格，我已经使用过了。
我向选定的表添加了插入、更新和删除触发器，然后检查了选定字段的更改。当时它很简单，而且运行良好。
以下是我对这种方法发现的问题：
审计表旧/新值字段必须是varchar(MAX)类型，以便能够处理可能被审计的所有不同值：int、bool、decimal、float、varchar等都必须适合
检查每个字段的代码编写和维护都很繁琐。也很容易错过一些东西（比如将空字段更改为值没有被捕获，因为NULL！= value是NULL。
删除记录：如何记录？所有字段？选定的字段？这变得复杂了
我的未来愿景是使用一些SQL-CLR代码，并编写一个通用触发器，该触发器在执行并检查表元数据以查看要审计的内容。其次，新/旧值将转换为XML字段，并记录整个对象：这会导致更多的数据，但删除会有一个完整的记录。网络上有几篇关于XML审计触发器的文章。
- Quango
0
触发器用于在您修改或插入特定表时执行操作，并且您可以在触发器中检查特定列。完整的示例和说明请参见以下网站。 http://www.allinworld99.blogspot.com/2015/04/triggers-in-sql.html
- Merbin Joe
-11
有一种通用的方法可以做到这一点。
CREATE TABLE [dbo].[Audit](
[TYPE] [CHAR](1) NULL,
[TableName] [VARCHAR](128) NULL,
[PK] [VARCHAR](1000) NULL,
[FieldName] [VARCHAR](128) NULL,
[OldValue] [VARCHAR](1000) NULL,
[NewValue] [VARCHAR](1000) NULL,
[UpdateDate] [datetime] NULL,
[UserName] [VARCHAR](128) NULL
) ON [PRIMARY]
- carlito
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接