UPDATE() 函式（插入和更新觸發器）

有沒有辦法讓這個函式中的列名動態化？例如，將參數傳遞給名為 @FldName = ‘Name’ 的變數。例如更新（@FldName）。基本上，我想做的是在我的觸發器中有一個統一或標準的程式碼。如果有人對我想要實現的目標有更好的解決方案或更好的方法，請提供幫助。謝謝。

出色的工作感謝 Nigel Rivett 分享他的工作……完美

http://www.nigelrivett.net/AuditTrailTrigger.html

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/

--Set up the tables
if exists (select * from sysobjects where id = object_id(N'[dbo].[Audit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Audit]
go
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
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i int not null, j int not null, s varchar(10), t varchar(10))
go
alter table trigtest add constraint pk primary key (i, j)
go

create trigger tr_trigtest on trigtest 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)

   select @TableName = 'trigtest'

   -- 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 @sql = @sql +     ' select ''' + @Type + ''''
           select @sql = @sql +     ',''' + @TableName + ''''
           select @sql = @sql +     ',' + @PKSelect
           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
go

我會使用動態sql：

DECLARE @fldname varchar(max) = 'foo'

Declare @sql NVARCHAR(MAX) =
'
UPDATE TABLE
SET ' + @FldName + ' = bar
WHERE ' + @FldName + ' <> bar '

EXEC(@sql)

在此範例中，這會將 @sql 設置為：

UPDATE TABLE
SET foo = bar
WHERE foo <> bar 

並執行腳本。顯然，您需要針對您的確切實現進行修改。

擴展：

因為您必須執行動態 sql 而實際上不能在函式中執行此操作，所以您必須將動態 sql 包裝在一個過程中才能執行此操作：

CREATE PROCEDURE dbo.update_field (@fldname VARCHAR(MAX)) 
AS 

Declare @sql NVARCHAR(MAX) =
'
UPDATE TABLE
SET ' + @FldName + ' = bar
WHERE ' + @FldName + ' <> bar '
EXEC(@sql)

CREATE FUNCTION UPDATE_FIELD (@fldname VARCHAR(MAX))
RETURNS int
AS
BEGIN
EXEC dbo.update_field @fldname
RETURN 0
END

所有這一切 - 如果您被迫將其作為解決方案實施，我強烈建議您審查您的架構。

引用自：https://dba.stackexchange.com/questions/97422