GO
/****** 对象:  StoredProcedure [dbo].[pro_GenerateTrigger]    脚本日期: 08/13/2012 10:10:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pro_GenerateTrigger]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[pro_GenerateTrigger]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*****************************************************
** DECRIPTION: sql生成（mssql对于表的delete和update做备份的触发器）代码的存储过程
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** V000.0.1    pukuimin     08/04/2012							新建程序
** --------  ------------  ------------  -----------------   -------------------------------
*******************************************************/
create procedure [dbo].[pro_GenerateTrigger](
					@TableName NVARCHAR(200) ---表名
)
--WITH ENCRYPTION ---加锁
as
begin
DECLARE @allColumns	VARCHAR(3000) --所有列名
DECLARE @ConstructParams     VARCHAR(8000)  --构造参数
DECLARE @ConstructGetValue     VARCHAR(8000) --构造赋值
DECLARE @FieldPropertys     VARCHAR(8000) --属性和字段
DECLARE @Uni_Primary     VARCHAR(100) --唯一键或主键
SELECT @ConstructParams = '',@FieldPropertys='',@ConstructGetValue='',@allColumns='',@Uni_Primary=''

if isnull(@TableName,'')=''
begin
	print '表名不能为空！'
	return 0
end 

set @Uni_Primary=dbo.fun_get_unique_column(@TableName)
if(@Uni_Primary='') set @Uni_Primary=dbo.fun_get_PrimaryKey(@TableName)
if(@Uni_Primary='') set @Uni_Primary='id'

SELECT 
@allColumns=@allColumns+COLUMN_NAME+','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName 

set @allColumns=LEFT(@allColumns,LEN(@allColumns)-1)


print 'SET ANSI_NULLS ON'
print 'GO'
print 'SET QUOTED_IDENTIFIER ON'
print 'GO'
print 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[trigger_delete_update_'+@TableName+']''))'
print 'DROP TRIGGER [dbo].[trigger_delete_update_'+@TableName+']'
print 'GO'
print 'CREATE TRIGGER [trigger_delete_update_'+@TableName+']'
print 'on '+@TableName
print 'FOR DELETE,UPDATE'
print 'AS'
	print dbo.fun_get_tabspace(1)+'BEGIN'
		print dbo.fun_get_tabspace(2)+'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[stuinfo_bak]'') AND type in (N''U''))'
			print dbo.fun_get_tabspace(3)+'BEGIN'
				print dbo.fun_get_tabspace(4)+'select * into ['+@TableName+'_bak] from [stuinfo] where  1 = 2'
			print dbo.fun_get_tabspace(3)+'END'
		print dbo.fun_get_tabspace(2)+'SET IDENTITY_INSERT ['+@TableName+'_bak] ON'
		print dbo.fun_get_tabspace(2)+'INSERT INTO ['+@TableName+'_bak]('+@allColumns+') select '+@allColumns+' from deleted'
		print dbo.fun_get_tabspace(2)+'SET IDENTITY_INSERT ['+@TableName+'_bak] OFF'
	print dbo.fun_get_tabspace(1)+'END'
end
/*

exec [pro_GenerateTrigger] 'stuinfo'

select row_number() over(order by id asc) row_num,* from stuinfo_bak 

*/