sql server中如何通过触发器判断相同字段备份表格，以适应后期两表格字段不一致的可能性

你好

CREATE  TRIGGER  [dbo].[触发器名] ON [dbo].[表1] 
FOR   DELETE 
AS
--定义相同字段，由于后面要通过sql变量执行，所以必须使用nvarchar类型
Declare @SameCol nvarchar(max)

select a.name,convert(int,1) ID into #tabTT from ( 
   select b.name from sysobjects a  left join syscolumns b on a.id = b.id where a.name ='表1'
) a 
left join ( 
   select b.name from sysobjects a left join syscolumns b on a.id = b.id where a.name ='备份表'
) b on a.name = b.name  
where ISNULL(b.name,'') <> ''

--相同字段组合成字符串
select ID,
       stuff((select ','+name from  #tabTT  
            where c.ID=ID 
            for xml path('')),1,1,'') as name
into #tabTB
from #tabTT c   
group by c.ID

select @SameCol=name from #tabTB

drop table #tabTT
drop table #tabTB

--必须先将触发器表格放到临时表，不然创建后sql对象是无法访问deleted和updated的
select * into #tabCC from deleted

--获取电脑名称
Declare @Mach nvarchar(50)  
select @Mach= a.host_name from sys.dm_exec_sessions a , sys.dm_exec_connections b
	where a.session_id=b.session_id and a.session_id = @@SPID

--组装sql语句
declare @sql nvarchar(max);
set @sql='	insert into 备份表(Dodate,'+@SameCol+')'+
'select Convert(varchar(23), GetDate(),21)+''_''+'''+@Mach+''','+@SameCol+'  from  #tabCC'
--print @sql
EXECUTE sp_executesql @sql

drop table #tabCC
GO