/*
触发器获取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(