SQL Server 自动审核更新列_数据库问题-得得之家

本文介绍了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

这篇关于SQL Server 自动审核更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

SQL Server 自动审核更新列

问题描述

相关文档推荐