UPDATE()UPDATE和INSERT触发器的功能 [英] UPDATE() Function of UPDATE and INSERT Trigger

查看:196
本文介绍了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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆