264.生成记录插入SQL语句的触发器

我就是我 2023-12-27 06:55 4阅读 0赞

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_createtrigger]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[p_createtrigger]
    GO
    
    /*--生成记录插入SQL语句的触发器
    
    
    	调用这个存储过程,就可以为指定表直接生成一个记录操作的触发器
    	注意修改存储过程中的**部分
    
    */
    
    /*--调用示例
    
    	exec p_createtrigger 'a'
    --*/
    create proc p_createtrigger
    @tbname sysname,	--为那个表生成处理的触发器
    @triggername sysname=''	--生成的触发器名,不指定的话,自动命名
    as
    declare @sqlhead nvarchar(4000),@sqlend nvarchar(4000)
    	,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000)
    	,@sql41 nvarchar(4000),@sql42 nvarchar(4000)
    	,@i int,@ic varchar(20)
    
    set nocount on
    
    --生成处理临时表
    select id=identity(int,1,1),gid=0
    	,fdname,sv=case 
    		when type like '%int' or type like '%money' 
    			or type in('float','real','decimal','numeric')
    		then 'case when '+fdname+' is null then ''NULL'' else convert(varchar,'+fdname+') end'  
    		when type like '%datetime' or type like '%binary'
    			or type in('timestamp','uniqueidentifier')
    		then 'case when '+fdname+' is null then ''NULL'' else ''''''''+replace(convert(varchar,'+fdname+'),'''''''','''''''''''')+'''''''' end'
    		else 'case when '+fdname+' is null then ''NULL'' else ''''''''+replace('+fdname+','''''''','''''''''''')+'''''''' end'
    	end
    into # from(
    	select fdname='['+replace(a.name,']',']]')+']'
    		,type=b.name,a.colid
    	from syscolumns a
    		join systypes b on a.xtype=b.xtype
    	where b.name not in('image','ntext','text','sql_variant')	--不能处理的类型
    		and id=object_id(@tbname)
    )a order by colid
    
    if @@rowcount=0 return	--如果没有满足条件的数据,退出
    
    --判断需要多少个变量来处理
    select @i=max(len(sv)) from #
    set @i=3800/@i
    
    --分组临时表
    update # set gid=id/@i
    select @i=max(gid) from #
    
    --生成数据处理语句
    select @sql1='',@sql2='',@sql3='',@sql41='',@sql42=''
    
    while @i>=0
    	select @ic=cast(@i as varchar),@i=@i-1
    		,@sql1='@a'+@ic+' nvarchar(4000),@b'+@ic+' nvarchar(4000),'+@sql1
    		,@sql2='@a'+@ic+'='''',@b'+@ic+'='''','+@sql2
    		,@sql3='select @a'+@ic+'=@a'+@ic+'+'',''+fdname,@b'
    			+@ic+'=@b'+@ic+'+''+'''',''''+''+sv from # where gid='+@ic+char(13)+@sql3
    		,@sql41='+@a'+@ic+@sql41
    		,@sql42='+@b'+@ic+@sql42
    
    select @sql1=left(@sql1,len(@sql1)-1)
    	,@sql2=left(@sql2,len(@sql2)-1)
    	,@sql3=left(@sql3,len(@sql3)-1)
    	,@sql41=substring(@sql41,2,4000)
    	,@sql42=substring(@sql42,2,4000)
    	,@tbname='['+replace(@tbname,']',']]')+']'
    	,@triggername='['+case isnull(@triggername,'')
    		when ''then cast(newid() as varchar(36))
    		else replace(@triggername,']',']]') end+']'
    
    exec('
    declare '+@sql1+'
    select '+@sql2+'
    '+@sql3+'
    select @a0=stuff(@a0,1,1,''''),@b0=stuff(@b0,1,5,'''')
    exec(''
    create trigger '+@triggername+' on '+@tbname+'
    for insert
    as
    insert 记录表(SQL语句) --------**这里的记录表,SQL语句字段名要根据实际情况修改
    select ''''insert '+@tbname+'(''+'+@sql41+'+'')
    values(''''+''+'+@sql42+'+''+'''')'''' 
    from inserted'')
    ')
    go