sql server - Execute T-SQL stored procedure in update trigger with select from inserted as a parameter -
trying pass updated value stored procedure parameter inside update trigger. correct syntax achieve if possible?
i looking way use select directly in call stored procedure (using execute). there don't seem other way inside update trigger retrieve updated value
select inserted most relevant solution somehow move select inside called stored procedure if returns record set can't modify , have scalar pass. don't have use local variable pass value, seems possible not elegant. thanks.
if want column names , columns values got affected during update , save in audit table in database may try below trigger
alter trigger [dbo].[tr_location_audit] on [dbo].[location] update 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 need change @tablename match table audited. -- here made guests example. select @tablename = 'location' -- date , user select @username = system_user , @updatedate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114) -- action if exists (select * inserted) if exists (select * deleted) select @type = 'u' else select @type = 'i' else select @type = 'd' -- list of columns select * #ins inserted select * #del deleted -- primary key columns full outer join select @pkcols = coalesce(@pkcols + ' and', ' on') + ' i.' + c.column_name + ' = d.' + c.column_name 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 -- primary key select insert select @pkselect = coalesce(@pkselect+'+','') + '''<' + column_name + '=''+convert(varchar(100), coalesce(i.' + column_name +',d.' + column_name + '))+''>''' 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 if @pkcols null begin raiserror('no pk on table %s', 16, -1, @tablename) return end select @field = 0, @maxfield = max(ordinal_position) information_schema.columns table_name = @tablename while @field < @maxfield begin select @field = min(ordinal_position) information_schema.columns table_name = @tablename , 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 information_schema.columns table_name = @tablename , 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 + '''' + ' #ins full outer join #del d' + @pkcols + ' i.' + @fieldname + ' <> d.' + @fieldname + ' or (i.' + @fieldname + ' null , d.' + @fieldname + ' not null)' + ' or (i.' + @fieldname + ' not null , d.' + @fieldname + ' null)' exec (@sql) end end
Comments
Post a Comment