Изменения записи журнала на сервере SQL в таблице аудита

Ответ 1

Взгляните на эту статью на 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) СОЗДАТЬ триггер UPDATE в таблице GUESTS следующим образом.

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

GO

Ответ 2

Я знаю, что это старо, но, возможно, это поможет кому-то другому.

Не записывайте новые значения. Ваши существующие таблицы, GUESTS, имеют новые значения. У вас будет двойной ввод данных, плюс ваш размер базы данных будет слишком быстро развиваться.

Я очистил это и минимизировал его для этого примера, но вот таблицы, которые вам нужны для регистрации изменений:

CREATE TABLE GUESTS (
      GuestID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

CREATE TABLE GUESTS_LOG (
      GuestLogID INT IDENTITY(1,1) PRIMARY KEY, 
      GuestID INT, 
      GuestName VARCHAR(50), 
      ModifiedBy INT, 
      ModifiedOn DATETIME
)

Когда значение изменяется в таблице GUESTS (например: имя гостя), просто выйдите из этой цельной строки данных, как есть, в свою таблицу журнала/аудита с помощью триггера. Ваша таблица GUESTS имеет текущие данные, таблица журнала/аудита имеет старые данные.

Затем используйте оператор select для получения данных из обеих таблиц:

SELECT 0 AS 'GuestLogID', GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS] WHERE GuestID = 1
UNION
SELECT GuestLogID, GuestID, GuestName, ModifiedBy, ModifiedOn FROM [GUESTS_LOG] WHERE GuestID = 1
ORDER BY ModifiedOn ASC

Ваши данные будут выглядеть так: от Oldest to Newest, первая строка - это то, что было создано, а последняя строка - текущие. Вы можете точно увидеть, что изменилось, кто изменил его, и когда он изменил его.

Возможно, у меня была функция, которая зацикливалась на RecordSet (в классическом ASP) и отображала только те значения, которые были изменены на веб-странице. Он сделал для БОЛЬШОГО аудита, чтобы пользователи могли видеть, что со временем изменилось.

Ответ 3

Эй Это очень просто увидеть это

@OLD_GUEST_NAME = d.GUEST_NAME из удаленного d;

эта переменная сохранит ваше старое удаленное значение, а затем вы можете вставить его там, где хотите.

например -

Create trigger testupdate on test for update, delete
  as
declare @tableid varchar(50);
declare @testid varchar(50);
declare @newdata varchar(50);
declare @olddata varchar(50);


select @tableid = count(*)+1 from audit_test
select @testid=d.tableid from inserted d;
select @olddata = d.data from deleted d;
select @newdata = i.data from inserted i;

insert into audit_test (tableid, testid, olddata, newdata) values (@tableid, @testid, @olddata, @newdata)

go