触发器获取修改表的SQL语句

最新推荐文章于 2023-11-09 18:26:52 发布

yujiang930

最新推荐文章于 2023-11-09 18:26:52 发布

阅读量1.3k

点赞数

文章标签： sql insert join object delete null

本文链接：https://blog.csdn.net/yujiang930/article/details/5254656

版权

/*
触发器获取SQL语句增量传输
功能：捕捉修改表的SQL语句
使用说明： 1、先新建一表手动写入主键信息或者唯一索引
   Create table prmary_key
   (tab_name varchar(255),
    key_name varchar(255))
   --此表仅在建立触发器时使用，建完所有触发器后记得删除
   2、建触发器仅需要修改@tab_name变量，即可
Create By Yujiang
*/
Declare @cursql varchar(8000),
@cursqltmp varchar(8000),
@curkey  Varchar(500), --主键或唯一索引
@curkeytmp Varchar(2000), --主键循环用
@curkeywhere Varchar(1000), --主键条件
@curkeyjoin Varchar(1000), --关联条件
@curexecsql varchar(5000), --执行SQL
@curcols varchar(2000), --所有的列名
@curcolstmp varchar(2000), --循环用
@tab_name varchar(255),
@curtmp  varchar(255), --循环用
@curcoltype varchar(255) --字段数据类型

Set @tab_name = 'tj_suggestion' --★需要手动修改

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)
  +'    Goto Up' + 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 ' + char(13) + char(10) + char(13) + char(10)

  --生成插入
        Select @cursqltmp = 'Ins: ' + char(13) + char(10)
      + ' Insert into TJ_UPDATE (CJRQ,ZT,GXSQL) '+ char(13) + char(10)
      + ' Select Getdate(),''0'',''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 @curcoltype = t.name
        From syscolumns c
        Inner join systypes t on (c.xtype = t.xtype and t.xtype = t.xusertype)
        Where c.id = object_id(@tab_name) and c.name = @curtmp

       if (lower(@curcoltype) = 'char' or lower(@curcoltype) = 'varchar' or lower(@curcoltype) = 'smalldatetime' or lower(@curcoltype) = 'datetime'
        or lower(@curcoltype) = 'nchar' or lower(@curcoltype) = 'ntext' or lower(@curcoltype) = 'nvarchar')
    Select @cursqltmp = @cursqltmp + '  + Isnull(char(39) + Rtrim(CAST(' + @curtmp + ' as varchar)) + char(39),''NULL'') + '',''' + char(13) + char(10)
       else
       Select @cursqltmp = @cursqltmp + '  + Isnull( Rtrim(CAST(' + @curtmp + ' as varchar)),''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)
      + '  Return'+ char(13) + char(10)
  Select @cursql = @cursql + @cursqltmp + char(13) + char(10)

  --生成修改
        Select @cursqltmp = 'Up: ' + char(13) + char(10)
      + ' Insert into TJ_UPDATE (CJRQ,ZT,GXSQL) '+ char(13) + char(10)
      + ' Select Getdate(),''0'',''Update ' + @tab_name + '''' + char(13) + char(10)
      + ' + '' Set '''

   --每个列都更新
   Declare @ii int,@tmp varchar(100)
   Set @ii = 1
   Select @curcolstmp = @curcols + ','
   while charindex(',',@curcolstmp) > 0
     Begin
       Select @curtmp = left(@curcolstmp,charindex(',',@curcolstmp) - 1)

       Select @curcoltype = t.name
        From syscolumns c
        Inner join systypes t on (c.xtype = t.xtype and t.xtype = t.xusertype)
        Where c.id = object_id(@tab_name) and c.name = @curtmp

       if @ii = 1
            Select @cursqltmp = @cursqltmp + '  + isnull('''+ @curtmp + ' = '''+ @tmp +' Rtrim(CAST(inserted.'+ @curtmp + ' as Varchar)) '+ @tmp +' '''','''')' + char(13) + char(10)
       else
            Select @cursqltmp = @cursqltmp + '  + isnull('','+ @curtmp + ' = '' '+ @tmp +' Rtrim(CAST(inserted.'+ @curtmp + ' as Varchar)) '+ @tmp +' '''','''')' + char(13) + char(10)
              Select @curcolstmp = substring(@curcolstmp,charindex(',',@curcolstmp) + 1 ,len(@curcolstmp))
       set @ii = @ii + 1
     End

--去掉最后一个,号
select @cursqltmp = substring(@cursqltmp,1,len(@cursqltmp) - 2)

--检索主键作为where条件,条件取自deleted表数据取之inserted表
Select @cursqltmp = @cursqltmp + '+'' Where '

   Select @curkeytmp = @curkey
   Select @curkeywhere = ''
   Select @curkeyjoin = ''
   while Charindex(',',@curkeytmp) > 0
       Begin
    Select @curtmp = left(@curkeytmp,charindex(',',@curkeytmp) - 1)

     Select @curcoltype = t.name
        From syscolumns c
        Inner join systypes t on (c.xtype = t.xtype and t.xtype = t.xusertype)
        Where c.id = object_id(@tab_name) and c.name = @curtmp

    --主键肯定不会为空
    Select @curkeyjoin = @curkeyjoin + ' Inserted.' + @curtmp + ' = Deleted.' + @curtmp + ' And '
    Select @curkeywhere = @curkeywhere + @curtmp + '='''+ @tmp +'RTrim(Cast(deleted.' + @curtmp + ' As Varchar)) '+ @tmp +''' And '
    Select @curkeytmp = substring(@curkeytmp,charindex(',',@curkeytmp) + 1 ,len(@curkeytmp))
       End
   --调整join和where结束符
   Select @curkeyjoin = substring(@curkeyjoin,1,len(@curkeyjoin) - 4)
   Select @curkeywhere = substring(@curkeywhere,1,len(@curkeywhere) - 7)

   --from子句
   Select @cursqltmp = @cursqltmp + @curkeywhere + ' From inserted' + char(13) + char(10)
       + '   Inner Join deleted on ('
       + @curkeyjoin + ')' + char(13) + char(10)
       + '  Return'

Select @cursql = @cursql + @cursqltmp + char(13) + char(10) + char(13) + char(10) + char(13) + char(10)

  --生成删除
  Select @cursqltmp = 'Del:' + char(13) + char(10)
      + '  Insert into TJ_UPDATE (CJRQ,ZT,GXSQL)' + char(13) + char(10)
      + '  Select Getdate(),''0'',''Delete ' + @tab_name + ' where ''+' + char(13) + char(10)
   --检索主键
   Select @curcolstmp = @curkey
   while Charindex(',',@curcolstmp) > 0
       Begin
    Select @curtmp = left(@curcolstmp,charindex(',',@curcolstmp) - 1)

    Select @curcoltype = t.name
        From syscolumns c
        Inner join systypes t on (c.xtype = t.xtype and t.xtype = t.xusertype)
        Where c.id = object_id(@tab_name) and c.name = @curtmp

    --主键肯定不会为空
    Select @cursqltmp = @cursqltmp + '   ''' + @curtmp + ' = '''+ @tmp +' RTrim(CAST('+ @curtmp + ' As Varchar))'+ @tmp +' ''And ''+' + char(13) + char(10)
    Select @curcolstmp = substring(@curcolstmp,charindex(',',@curcolstmp) + 1 ,len(@curcolstmp))
       End
   Select @cursqltmp = left(@cursqltmp,len(@cursqltmp) - 13) + ' From deleted' + char(13) + char(10)
  Select @cursql = @cursql + @cursqltmp + char(13) + char(10)


  Set @cursql = @cursql +' End'
-- print @cursql
-- return
Exec(@cursql)

Print 'ok!...........创建成功!'