在审计表中记录SQL Server中的记录更改

桌子：

CREATE TABLE GUESTS (
      GUEST_ID int IDENTITY(1,1) PRIMARY KEY,GUEST_NAME VARCHAR(50),GUEST_SURNAME VARCHAR(50),ADRESS VARCHAR(100),CITY VARCHAR(50),CITY_CODE VARCHAR(10),COUNTRY VARCHAR(50),STATUS VARCHAR(20),COMMENT nvarchar(max);

对于日志记录：

CREATE TABLE AUDIT_GUESTS (
  ID int IDENTITY(1,GUEST_ID int,OLD_GUEST_NAME VARCHAR(50),NEW_GUEST_NAME VARCHAR(50),OLD_GUEST_SURNAME VARCHAR(50),NEW_GUEST_SURNAME VARCHAR(50),OLD_ADRESS VARCHAR(100),NEW_ADRESS VARCHAR(100),OLD_CITY VARCHAR(50),NEW_CITY VARCHAR(50),OLD_CITY_CODE VARCHAR(10),NEW_CITY_CODE VARCHAR(10),OLD_COUNTRY VARCHAR(50),NEW_COUNTRY VARCHAR(50),OLD_STATUS VARCHAR(20),NEW_STATUS VARCHAR(20),OLD_COMMENT nvarchar(max),NEW_COMMENT nvarchar(max),AUDIT_ACTION varchar(100),AUDIT_TIMESTAMP datetime);

我想在我的GUESTS表上创建一个触发器来记录我的AUDIT_GUESTS表中的所有更改.如何在sql Server 2014 Express中执行此操作？

我试过了：

create TRIGGER trgAfterUpdate ON [dbo].[GUESTS] 
FOR UPDATE
AS
    declare @GUEST_ID int;
    declare @GUEST_NAME varchar(50);
    declare @GUEST_SURNAME VARCHAR(50);
    declare @ADRESS VARCHAR(100); 
    declare @CITY VARCHAR(50);
    declare @CITY_CODE VARCHAR(10); 
    declare @COUNTRY VARCHAR(50);
    declare @STATUS VARCHAR(20);
    declare @COMMENT nvarchar(max);
    declare @AUDIT_ACTION varchar(100);
    declare @AUDIT_TIMESTAMP datetime;

    select @GUEST_ID=i.GUEST_ID from inserted i;            
    select @GUEST_NAME=i.GUEST_NAME from inserted i;    
    select @GUEST_SURNAME=i.GUEST_SURNAME from inserted i;
    select @ADRESS=i.ADRESS from inserted i;
    select @CITY=i.CITY from inserted i;
    select @CITY_CODE=i.CITY_CODE from inserted i;
    select @COUNTRY=i.COUNTRY from inserted i;
    select @STATUS=i.STATUS from inserted i;
    select @COMMENT=i.COMMENT from inserted i;

        if update(GUEST_NAME)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(GUEST_SURNAME)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(ADRESS)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(CITY)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(CITY_CODE)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(COUNTRY)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(STATUS)
        set @audit_action='Updated Record -- After Update Trigger.';

        if update(COMMENT)
        set @audit_action='Updated Record -- After Update Trigger.';

        insert into AUDIT_GUESTS
           (GUEST_ID,GUEST_NAME,GUEST_SURNAME,ADRESS,CITY,CITY_CODE,COUNTRY,STATUS,COMMENT,audit_action,AUDIT_TIMESTAMP) 
    values(@GUEST_ID,@GUEST_NAME,@GUEST_SURNAME,@ADRESS,@CITY,@CITY_CODE,@COUNTRY,@STATUS,@COMMENT,@audit_action,getdate());
    GO

工作好,但我想看看旧的新价值观.

在sqlite我有：

CREATE TRIGGER [LOG_UPDATE]
AFTER UPDATE OF [GUEST_NAME],[GUEST_SURNAME],[ADRESS],[CITY],[CITY_CODE],[COUNTRY],[STATUS],[COMMENT]
ON [GUESTS]
BEGIN
INSERT INTO GUESTS_LOG
 ( GUEST_ID,NAME_OLD,NAME_NEW,SURNAME_OLD,SURNAME_NEW,ADRESS_OLD,ADRESS_NEW,CITY_OLD,CITY_NEW,CITY_CODE_OLD,CITY_CODE_NEW,COUNTRY_OLD,COUNTRY_NEW,STATUS_OLD,STATUS_NEW,COMMENT_OLD,COMMENT_NEW,sqlAction,DATE_TIME)   

   VALUES   

 (OLD.GUEST_ID,OLD.GUEST_NAME,NEW.GUEST_NAME,OLD.GUEST_SURNAME,NEW.GUEST_SURNAME,OLD.ADRESS,NEW.ADRESS,OLD.CITY,NEW.CITY,OLD.CITY_CODE,NEW.CITY_CODE,OLD.COUNTRY,NEW.COUNTRY,OLD.STATUS,NEW.STATUS,OLD.COMMENT,NEW.COMMENT,'record changed',datetime('now','localtime'));  

END

它工作正常只是不知道如何传递给sql服务器.刚刚开始学习

解决方法

看看 this article在Simple-talk.com Pop Rivett.它引导您创建一个通用触发器,将记录所有更新的列的OLDVALUE,NEWVALUE.该代码非常通用,您可以将其应用于您要审核的任何表,也可以用于任何CRUD操作,即INSERT,UPDATE和DELETE.唯一的要求是,要审核的表应该有一个PRIMARY KEY(最好设计的表应该有).

以下是您的GUESTS表相关代码.

1)创建AUDIT表.

IF NOT EXISTS
      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[Audit]') 
               AND OBJECTPROPERTY(id,N'IsUserTable') = 1)
       CREATE TABLE Audit 
               (Type CHAR(1),TableName VARCHAR(128),PK VARCHAR(1000),FieldName VARCHAR(128),OldValue VARCHAR(1000),NewValue VARCHAR(1000),UpdateDate datetime,UserName VARCHAR(128))
GO

2)在GUESTS表上创建一个UPDATE触发器,如下所示.

CREATE TRIGGER TR_GUESTS_AUDIT ON GUESTS FOR UPDATE
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. 
-- Here we made GUESTS for your example.
SELECT @TableName = 'GUESTS'

-- date and user
SELECT         @UserName = SYSTEM_USER,@UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

-- 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 + ')'
       + ',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

GO

在审计表中记录SQL Server中的记录更改

解决方法

猜你在找的MsSQL相关文章