发布一下自己写的通用触发器获取SQL的语句，还请各位提下意见
update 是在没办法了只好先delete 在 insert
各位有没有更好办法
/*触发器获取SQL语句增量传输功能：捕捉修改表的SQL语句使用说明： 1、先新建一表手动写入主键信息或者唯一索引 Create table prmary_key ( tab_name varchar(255),key_name varchar(255)) --此表仅在建立触发器时使用，建完所有触发器后记得删除 2、建触发器仅需要修改@tab_name变量，即可 3、update语句为2条先删除，后insertCreate By Yujiang*/Declare @cursql varchar(8000), @cursqltmp varchar(8000), @curkey Varchar(500), --主键或唯一索引 @curexecsql varchar(5000), --执行SQL @curcols varchar(2000), --所有的列名 @curcolstmp varchar(2000), --循环用 @tab_name varchar(255), @curtmp varchar(255) --循环用 Set @tab_name = 'TJ_ZHXM_HD' --★需要手动修改Select @cursql = ' if exists(select * from sysobjects where name = '+ char(39) + 'tr_' + @tab_name +'_ZLYJ' + char(39) + ' and type = ''TR'')' + char(13) + char(10) + ' drop trigger tr_'+ @tab_name + '_ZLYJ'Exec(@cursql)--获取主键Select @curkey = key_name from prmary_key where tab_name = @tab_nameif (@curkey is Null or @curkey = '')Begin Print @tab_name + '没有主键或唯一索引无法捕捉SQL语句' ReturnEndSet @curcols = ''Set @cursqltmp = ''if right(@curkey,1) <> ',' Set @curkey = @curkey + ',' declare @col_name varchar(50) Declare #tmp_cur cursor for select name from syscolumns where id = object_id(@tab_name) open #tmp_cur fetch next from #tmp_cur into @col_name while @@fetch_status = 0 Begin Set @curcols = @curcols + @col_name + ',' fetch next from #tmp_cur into @col_name End close #tmp_cur deallocate #tmp_cur--去掉后面的引号Select @curcols = left(@curcols,len(@curcols) - 1)Select @cursql = ' Create Trigger tr_'+ @tab_name + '_ZLYJ' + char(13) + char(10) + ' On ' + @tab_name + char(13) + char(10) + ' For Insert,Update,Delete' + char(13) + char(10) + ' AS ' + char(13) + char(10) + ' Begin' + char(13) + char(10) + ' Declare @sql varchar(8000), '+ char(13) + char(10) + ' @sqltmp varchar(1000), '+ char(13) + char(10) + ' @Nsql Nvarchar(3000),'+ char(13) + char(10) + ' @key varchar(255),'+ char(13) + char(10) + ' @tmp_key varchar(50),'+ char(13) + char(10) + ' @ntmp Nvarchar(50),'+ char(13) + char(10) + ' @cols varchar(2000),'+ char(13) + char(10) + ' @coltmp varchar(255),'+ char(13) + char(10) + ' @inscol varchar(250),'+ char(13) + char(10) + ' @delcol varchar(250),'+ char(13) + char(10) + ' @updateflag varchar(1), --1表示是更新'+ char(13) + char(10) + ' @updateset varchar(3000),'+ char(13) + char(10) + ' @ii int,'+ char(13) + char(10) + ' @ins_cnt int,'+ char(13) + char(10) + ' @del_cnt int '+ char(13) + char(10) + ' Select @ii = 0 '+ char(13) + char(10) + ' Select @ins_cnt = count(1) from inserted'+ char(13) + char(10) + ' Select @del_cnt = count(1) from deleted'+ char(13) + char(10) + ' --新增'+ char(13) + char(10) + ' If (@ins_cnt > 0 And @del_cnt = 0)'+ char(13) + char(10) + ' Begin'+ char(13) + char(10) + ' Set @updateflag = ''0'''+ char(13) + char(10) + ' Goto Ins' + char(13) + char(10) + ' Return' + char(13) + char(10) + ' End'+ char(13) + char(10) +' --修改' + char(13) + char(10) +' If (@ins_cnt > 0 And @del_cnt > 0)' + char(13) + char(10) +' Begin' + char(13) + char(10) +' Set @updateflag = ''1'''+ char(13) + char(10) + ' Goto Del' + char(13) + char(10) +' Return ' + char(13) + char(10) +' End' + char(13) + char(10) +' --删除' + char(13) + char(10) +' If (@ins_cnt = 0 And @del_cnt > 0)' + char(13) + char(10) +' Begin' + char(13) + char(10) +' Set @updateflag = ''0'''+ char(13) + char(10) +' Goto Del' + char(13) + char(10) +' Return ' + char(13) + char(10) +' End' + char(13) + char(10) + char(13) + char(10) + char(13) + char(10) +' return ' --删除 Select @cursqltmp = 'Del:' + char(13) + char(10) + ' Insert into Test(sql)' + char(13) + char(10) + ' Select ''Delete ' + @tab_name + ' where ''+' + char(13) + char(10) --检索主键 Select @curcolstmp = @curkey while Charindex(',',@curcolstmp) > 0 Begin Select @curtmp = left(@curcolstmp,charindex(',',@curcolstmp) - 1) --主键肯定不会为空 Select @cursqltmp = @cursqltmp + ' ''CAST(' + @curtmp + ' AS VARCHAR) = ''+ char(39) + CAST(Rtrim('+ @curtmp + ') AS VARCHAR) + char(39) + And' + char(13) + char(10) Select @curcolstmp = substring(@curcolstmp,charindex(',',@curcolstmp) + 1 ,len(@curcolstmp)) End Select @cursqltmp = left(@cursqltmp,len(@cursqltmp) - 7) + ' From deleted' + char(13) + char(10) Select @cursqltmp = @cursqltmp + ' if @updateflag = ''1'' ' + char(13) + char(10) + ' Goto Ins' + char(13) + char(10) + ' else ' + char(13) + char(10) + ' Return'+ char(13) + char(10)+ char(13) + char(10)+ char(13) + char(10) Select @cursql = @cursql + @cursqltmp + char(13) + char(10) --插入 Select @cursqltmp = 'Ins: ' + char(13) + char(10) + ' Insert into Test(sql) '+ char(13) + char(10) + ' Select ''Insert into ' + @tab_name + '(' + @curcols + ')''+' + char(13) + char(10) + ' ''values(''' Select @curcolstmp = @curcols + ',' while charindex(',',@curcolstmp) > 0 Begin Select @curtmp = left(@curcolstmp,charindex(',',@curcolstmp) - 1) Select @cursqltmp = @cursqltmp + ' + Isnull(char(39) + Rtrim(CAST(' + @curtmp + ' as varchar) ) + char(39),''NULL'') + '',''' + char(13) + char(10) Select @curcolstmp = substring(@curcolstmp,charindex(',',@curcolstmp) + 1 ,len(@curcolstmp)) End Select @cursqltmp = left(@cursqltmp,len(@cursqltmp) - 6) + ''')'' from inserted' + char(13) + char(10) Select @cursql = @cursql + @cursqltmp + char(13) + char(10) Set @cursql = @cursql +' End'Exec(@cursql)Print 'ok!...........创建成功!'