Блог

Я использую приведенный ниже код в триггере. У меня есть два столбца, которые обновляются службой Windows каждые 60 минут в таблице, которую я отслеживаю. Как я могу игнорировать два столбца при записи в таблицу аудита.

 ALTER trigger [dbo].[tMonitors_ChangeTracking] on [dbo].[tMonitors] 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),
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = 'tMonitors'

-- 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 fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect ' ','')   ''''   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

select @PKValueSelect = coalesce(@PKValueSelect ' ','')   '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) amp; @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 tMonitors_Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)'
select @sql = @sql   ' select '''   @Type   ''''
select @sql = @sql   ','''   @TableName   ''''
select @sql = @sql   ','   @PKFieldSelect
select @sql = @sql   ','   @PKValueSelect
select @sql = @sql   ','''   @fieldname   ''''
select @sql = @sql   ',convert(varchar(1000),d.'   @fieldname   ')'
select @sql = @sql   ',convert(varchar(1000),i.'   @fieldname   ')'
select @sql = @sql   ','''   @UpdateDate   ''''
select @sql = @sql   ','''   @UserName   ''''
select @sql = @sql   ' from #ins i full outer join #del d'
select @sql = @sql   @PKCols
select @sql = @sql   ' where i.'   @fieldname   ' <> d.'   @fieldname 
select @sql = @sql   ' or (i.'   @fieldname   ' is null and  d.'   @fieldname   ' is not null)' 
select @sql = @sql   ' or (i.'   @fieldname   ' is not null and  d.'   @fieldname   ' is null)' 
exec (@sql)
end
end
  

Простой подход, предполагающий, что игнорируемые столбцы только обновляются во время этого ежечасного процесса:

 IF NOT (UPDATE(col_to_ignore_1) AND UPDATE(col_to_ignore_2))
BEGIN
    ... 
    EXEC(@sql);
END
  

В противном случае вы можете создать аналогичный список на основе INFORMATION_SCHEMA.COLUMNS, чтобы ведение журнала происходило только при обновлении хотя бы одного из других столбцов, что в конечном итоге выглядело бы следующим образом:

 IF (UPDATE(col_to_audit_1) OR UPDATE(col_to_audit_2) ... )
BEGIN
    ... 
    EXEC(@sql);
END
  

Из вашего вопроса неясно, хотите ли вы игнорировать эти два столбца всегда или только при выполнении этого ежечасного процесса. Возможно, другая идея состоит в том, чтобы использовать транзакцию и отключить триггер для этого обновления… очевидно, что это сделает триггер намного менее сложным.

Вы можете указать, на какие столбцы вы хотите обратить внимание, используя синтаксис «UPDATE(column_name)», например:

 CREATE TRIGGER trigger_name ON tablename
FOR insert, update, delete
AS
SET NOCOUNT ON
IF ( UPDATE(Column1) OR UPDATE(Column2))
BEGIN
  --your sql here
END
  

Другие параметры доступны в официальной документации.

Вы можете получить доступ к полному списку столбцов, измененных с помощью COLUMNS_UPDATED() , и сравнить его с предварительно определенными битовыми шаблонами. Для таблиц со многими столбцами вычисление позиций битов может быть проще, чем вывод списка UPDATED(every) OR UPDATED(other) OR UPDATED(column) .

Например, для таблицы:

 CREATE TABLE Users
(
    UserID INT NOT NULL PRIMARY KEY IDENTITY,
    Name nvarchar(255) NOT NULL,
    EmailAddress nvarchar(255) NOT NULL,
    LastPageViewDate datetime NULL,
    GroupID INT NOT NULL
);
  

где вы хотите игнорировать обновления для LastPageViewDate . Вы можете определить битовую маску с помощью:

 SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Users')
  

 name                 column_id
-------------------- -----------
UserID               1
Name                 2
EmailAddress         3
LastPageViewDate     4
GroupID              5
  

Затем триггер может проверить наличие битовой маски младшего порядка, 0x08 игнорируя завершающие 0 :

 CREATE TRIGGER dbo.TR_IUD_Users_LogEdit
    ON dbo.Users 
    AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Ignore page view logging caused by
    -- UPDATE Users SET LastPageViewDate = GETDATE() WHERE UserID = @UserID
    -- Bit 4 == LastPageViewDate
    IF COLUMNS_UPDATED() = 0x08 RETURN;

    -- For forward compatibility, you can ignore unchanged new columns added
    -- by RTRIM'ing the '0's (required since 0x08 != 0x0800)
    --
    --IF REPLACE(RTRIM(REPLACE(CONVERT(varchar(max), COLUMNS_UPDATED(), 2), '0', ' ')), ' ', '0') IN ('08')
    --  RETURN;

    /* Snipped trigger body */
END
GO