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語句的觸發(fā)器


	調(diào)用這個(gè)存儲(chǔ)過程,就可以為指定表直接生成一個(gè)記錄操作的觸發(fā)器
	注意修改存儲(chǔ)過程中的**部分

--鄒建 2004.05--*/

/*--調(diào)用示例

	exec p_createtrigger 'a'
--*/
create proc p_createtrigger
@tbname sysname,	--為那個(gè)表生成處理的觸發(fā)器
@triggername sysname=''	--生成的觸發(fā)器名,不指定的話,自動(dòng)命名
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

--生成處理臨時(shí)表
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	--如果沒有滿足條件的數(shù)據(jù),退出

--判斷需要多少個(gè)變量來處理
select @i=max(len(sv)) from #
set @i=3800/@i

--分組臨時(shí)表
update # set gid=id/@i
select @i=max(gid) from #

--生成數(shù)據(jù)處理語句
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語句字段名要根據(jù)實(shí)際情況修改
select ''''insert '+@tbname+'(''+'+@sql41+'+'')
values(''''+''+'+@sql42+'+''+'''')'''' 
from inserted'')
')
go