create TRIGGER dbo.tri_Export_Subsidiary_update
ON dbo.Export_Subsidiary
for UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql nvarchar(max)=''
select @sql=@sql+REPLACE('+case when a.%s=b.%s or (a.%s is null and b.%s is null) then '''' else '',[%s]:[''+isnull(cast(a.%s as nvarchar(max)),''NULL'')+'']->[''+isnull(cast(b.%s as nvarchar(max)),''NULL'')+'']'' end','%s',name)
from sys.syscolumns
where id=OBJECT_ID('Export_Subsidiary')
set @sql=stuff(@sql,1,1,'')
select * into #tmp_tri___Deleted from Deleted
select * into #tmp_tri___Inserted from Inserted
set @sql='insert financial_records_log(financialLog,datetime,operators)
SELECT ''Export_Subsidiary:''+cast(a.Id as varchar)+a.diffdata,convert(varchar(50),getdate(),121),''systemlog'' FROM(
select A.ID,'+@sql+' as diffdata
from #tmp_tri___Deleted a join #tmp_tri___Inserted b on a.id=b.id
) a where diffdata >''''
'
exec( @sql)
drop table #tmp_tri___Deleted
drop table #tmp_tri___Inserted
END
sql 触发器记录修改的字段和值
最新推荐文章于 2024-06-14 16:20:01 发布