UPDATE()UPDATE和INSERT触发器的功能 [英] UPDATE() Function of UPDATE and INSERT Trigger
本文介绍了UPDATE()UPDATE和INSERT触发器的功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有没有办法让列名在这个函数中动态化?例如,将参数传递给名为@FldName ='Name'的变量。例如UPDATE(@FldName)。基本上,我想要做的是在触发器内部有一个统一或标准的代码。如果有人有更好的解决方案或更好的方式达到我想达到的目标,请提供帮助。谢谢。
解决方案
这是我从宝贵的资源(Nigel Rivett)网站获得的解决方案代码。感谢Nigel分享他的作品。
http://www.nigelrivett.net/AuditTrailTrigger.html
- 获取列的列表
select * into #ins from inserted
select * into #del from deleted
- 获取主键完全外部联接的列
选择@PKCols = coalesce(@PKCols +'和','on')+'i。'+ c.COLUMN_NAME +'= d。'+ c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
其中pk.TABLE_NAME = @TableName
和CONSTRAINT_TYPE ='PRIMARY KEY'
和c.TABLE_NAME = pk.TABLE_NAME
和c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
- 获取主键选择插入
选择@PKSelect = coalesce(@PKSelect +'+','')+'''<来自INFORMATION_SCHEMA的'+ COLUMN_NAME +'=''+ convert(varchar(100),coalesce(i。'+ COLUMN_NAME +',d。'+ COLUMN_NAME +'))+''>'''
。 TABLE_CONSTRAIN TS pk,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
其中pk.TABLE_NAME = @TableName
和CONSTRAINT_TYPE ='PRIMARY KEY'
和c.TABLE_NAME = pk.TABLE_NAME
和c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
如果@PKCols为null
begin
raiserror('表%s上没有PK',16,-1,@ TableName)
返回
结束
从INFORMATION_SCHEMA.COLUMNS中选择@field = 0,@ maxfield = max(ORDINAL_POSITION),其中TABLE_NAME = @TableName
而@field< @maxfield
begin
从INFORMATION_SCHEMA.COLUMNS中选择@field = min(ORDINAL_POSITION),其中TABLE_NAME = @TableName和ORDINAL_POSITION> @field
选择@bit =(@ field - 1)%8 + 1
选择@bit = power(2,@ bit - 1)
select @char =((@ field - 1)/ 8)+ 1
if substring(COLUMNS_UPDATED(),@ char,1)& @bit> 0或@Type in('I','D')
begin
从INFORMATION_SCHEMA.COLUMNS中选择@fieldname = COLUMN_NAME,其中TABLE_NAME = @TableName和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 +''''
选择@sql = @sql +',转换(varchar(1000),d。'+ @ fieldname +')'
选择@sql = @sql +',转换(varchar(1000),i。'+ @ fieldname +')'
选择@sql = @sql +','''+ @UpdateDate +''''
select @ sql = @sql +','''+ @UserName +''''
从#ins中选择@sql = @sql +'我全外连接#del d'
选择@sql = @ sql + @PKCols
selec t @sql = @sql +'其中i。'+ @ fieldname +'<> d。'+ @ fieldname
选择@sql = @sql +'或(i。'+ @ fieldname +'为空,d。'+ @ fieldname +'不为空)'
select @ sql = @sql +'或(i。'+ @ fieldname +'不为空,d。'+ @ fieldname +'为空)'
exec(@sql)
end
端
Is there a way to have the column name dynamic in this function? For example, passing a parameter to a variable named @FldName = 'Name'. e.g. UPDATE(@FldName). Basically, what I want to do is to have a uniform or standard code inside my trigger. If anyone have better solutions or better way to what I want to achieve, please help. Thanks.
解决方案
Here is the solution code that I got from valuable resource (Nigel Rivett) website. Thanks to Nigel for sharing his work.
http://www.nigelrivett.net/AuditTrailTrigger.html
-- 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
这篇关于UPDATE()UPDATE和INSERT触发器的功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文