真爱无限的知识驿站

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   

*/