SQL Server 自动审核更新列
我需要在 SQL Server 上创建一个触发器，以便仅将更新的列写入审计表，其中包含特定表的表名、列名、以前/新值、日期、时间和用户.我该怎么做?
解决方案我通常有一个这样的表来存储审计数据我从这里得到了这个解决方案 https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-审计跟踪/
CREATE TABLE [dbo].[Audit]([AuditID] [int] IDENTITY(1,1) 非空，[类型] [字符](1) NULL,[表名] [varchar](128) NULL，[PK] [varchar](1000) NULL,[字段名称] [varchar](128) NULL，[OldValue] [varchar](1000) NULL,[NewValue] [varchar](1000) NULL,[更新日期] [日期时间] NULL，[用户名] [varchar](128) NULL，约束 [PK_Audit] 主键集群([AuditID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) 在 [主要]然后将以下触发器添加到我要审核的每个表
CREATE TRIGGER [dbo].[Tr_DB_Audit] ON [dbo].['YourTableName'] FOR INSERT, UPDATE, DELETE作为声明 @bit INT ,@field INT ,@maxfield INT ,@char INT，@fieldname VARCHAR(128) ,@TableName VARCHAR(128) ,@PKCols VARCHAR(1000) ,@sql VARCHAR(2000),@UpdateDate VARCHAR(21) ,@用户名 VARCHAR(128) ,@Type CHAR(1) ,@PKSelect VARCHAR(1000)--您将需要更改@TableName 以匹配要审核的表SELECT @TableName = 'NameOfTableYouWantToAudit'-- 日期和用户SELECT @UserName = SYSTEM_USER ,@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)- 行动如果存在(SELECT * FROM 插入)如果存在(SELECT * FROM 已删除)选择@Type = 'U'别的选择@Type = '我'别的选择@Type = 'D'-- 获取列列表SELECT * INTO #ins FROM 插入SELECT * INTO #del FROM 已删除-- 获取全外连接的主键列SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')+ '我.'+ c.COLUMN_NAME + ' = d.'+ c.COLUMN_NAME从 INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ，INFORMATION_SCHEMA.KEY_COLUMN_USAGE cWHERE pk.TABLE_NAME = @TableNameAND CONSTRAINT_TYPE = '主键'AND c.TABLE_NAME = pk.TABLE_NAMEAND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME-- 获取插入的主键选择SELECT @PKSelect = COALESCE(@PKSelect+'+','')+ '''<'+ COLUMN_NAME+ '=''+convert(varchar(100),合并(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''从 INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ，INFORMATION_SCHEMA.KEY_COLUMN_USAGE cWHERE pk.TABLE_NAME = @TableNameAND CONSTRAINT_TYPE = '主键'AND c.TABLE_NAME = pk.TABLE_NAMEAND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME如果@PKCols 为空开始RAISERROR('表 %s 上没有 PK', 16, -1, @TableName)返回结尾选择@field = 0,@maxfield = MAX(ORDINAL_POSITION)从 INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName当@field <@maxfield开始选择@field = MIN(ORDINAL_POSITION)来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @TableNameAND ORDINAL_POSITION >@场地SELECT @bit = (@field - 1 )% 8 + 1SELECT @bit = POWER(2,@bit - 1)SELECT @char = ((@field - 1)/8) + 1IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) &@位>0或 @Type IN ('I','D')开始SELECT @fieldname = COLUMN_NAME来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = @TableNameAND ORDINAL_POSITION = @field选择@sql = '插入审计(类型，表名，PK,字段名称，旧值，新价值，更新日期，用户名)选择 ''' + @Type + ''','''+ @TableName + ''',' + @PKSelect+ ',''' + @fieldname + ''''+ ',convert(varchar(1000),d.' + @fieldname + ')'+ ',convert(varchar(1000),i.' + @fieldname + ')'+ ',''' + @UpdateDate + ''''+ ',''' + @用户名 + ''''+ ' from #ins i 全外连接 #del d'+ @PKCols+ '我在哪里.'+ @fieldname + ' <>d.'+ @字段名+ ' 或 (i.' + @fieldname + ' 为空且 d.'+ @字段名+ ' 不为空)'+ ' 或 (i.' + @fieldname + ' 不为空且 d.'+ @字段名+ '为空)'执行 (@sql)结尾结尾你也可以看看这篇文章http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx>
I need to create a trigger on SQL Server to write only updated column into an audit table with table name, column name, previous/new value, date, time and user for specific tables. How can I do this?
解决方案i normally have a table like this to store the audit data and i got this solution from here https://www.simple-talk.com/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/
CREATE TABLE [dbo].[Audit](
[AuditID] [int] IDENTITY(1,1) NOT NULL,
[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,
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
[AuditID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
And then add the below trigger to every table i want to audit
CREATE TRIGGER [dbo].[Tr_DB_Audit] ON [dbo].['YourTableName'] FOR INSERT, UPDATE, DELETE
AS
DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000)
--You will need to change @TableName to match the table to be audited
SELECT @TableName = 'NameOfTableYouWantToAudit'
-- date and user
SELECT @UserName = SYSTEM_USER ,
@UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112)
+ ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
-- Action
IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
-- get list of columns
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted
-- Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
+ ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect+'+','')
+ '''<' + COLUMN_NAME
+ '=''+convert(varchar(100),
coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END
SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',' + @PKSelect
+ ',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END
You could also have a look at this article http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx
相关文章