update 是在没办法了只好先delete 在 insert
各位有没有更好办法
/*
触发器获取SQL语句增量传输
功能:捕捉修改表的SQL语句
使用说明: 1、先新建一表手动写入主键信息或者唯一索引
Create table prmary_key
( tab_name varchar(255),key_name varchar(255))
--此表仅在建立触发器时使用,建完所有触发器后记得删除
2、建触发器仅需要修改@tab_name变量,即可
3、update语句为2条先删除,后insert
Create 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_name
if (@curkey is Null or @curkey = '')
Begin
Print @tab_name + '没有主键或唯一索引无法捕捉SQL语句'
Return
End
Set @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!...........创建成功!'
各位有没有更好办法
/*
触发器获取SQL语句增量传输
功能:捕捉修改表的SQL语句
使用说明: 1、先新建一表手动写入主键信息或者唯一索引
Create table prmary_key
( tab_name varchar(255),key_name varchar(255))
--此表仅在建立触发器时使用,建完所有触发器后记得删除
2、建触发器仅需要修改@tab_name变量,即可
3、update语句为2条先删除,后insert
Create 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_name
if (@curkey is Null or @curkey = '')
Begin
Print @tab_name + '没有主键或唯一索引无法捕捉SQL语句'
Return
End
Set @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!...........创建成功!'
希望楼上的各位大哥给点宝贵的意见!
用于总分店数据同步总店修改资料后,下传到分店,然后执行SQL