ºÜ¶àʱºò£¬¶¼ÐèÒª¶ÔÊý¾Ý±í½øÐÐÀúÊ·¼Ç¼¡£±ÈÈçÿÐÞ¸ÄÒ»´Î±íµ¥£¬Ö®Ç°µÄ±íµ¥Êý¾Ý¶¼ÐèÒª¼ÆÈëÀúÊ·¡£µ±±íµ¥±È½Ï¶àµÄʱºò£¬¼Ç¼ÀúÊ·ÊÇÒ»¼þ±È½ÏÂé·³µÄÊÂÇé¡£ÓÖÒª½¨ÈÕÖ¾±í£¬ÓÖҪд´æ´¢¹ý³Ì£¬ÓÖÒªÐ´Ò³ÃæÂß¼µÈµÈ¡£ÓÐûÓÐͨÓõãµÄ°ì·¨ÄØ£¿×î½ü×öÏîĿʱÅöµ½ÁË£¬ÒªÇóÿ´ÎÉóºË¡¢Í˻صȲÙ×÷ʱ¾ÍÒª¼Ç¼±íÀúÊ·¡£ÓÚÊÇ£¬×÷Õß¾ÍÏëµ½ÁËÒÔÏ·½°¸¡£ÔÚ´ËÓë´ó¼Ò·ÖÏíÁË£¬Èç¹ûÓиüºÏÊʵĻòºÏÀíµÄ½¨Ò飬Çë»Ø¸´±¾Ìû¡£
1£©´´½¨ÈÕÖ¾±í
Ò»¸öÒ»¸ö½¨±íÊÇÒ»¼þ·³ÔêµÄÊ£¬¶øÇÒ»¹ÈÝÒ׳ö´í¡£ÄÇô£¬ÒÔÏ´洢¹ý³Ì¾ÍÄÜÅúÁ¿½¨±íÁË£¬»¹Ìí¼ÓÁËLogCreateDate¡¢LogDefaultFlag¡¢LogPTIDÕâ3¸ö×ֶΡ£ÖµµÃ×¢ÒâµÄÊÇ£¬´´½¨±í½á¹¹¿ÉÒÔÓÃÒÔÏÂÓï¾ä¡°SELECT
* Into tableName_Log FROM tableName¡±¡£Èç¹ûÖ»ÐèÒª¸´ÖƱí½á¹¹£¬ÄǾͲåÈëÒ»ÐУ¬ÔÙɾ³ý¾ÍÊÇ¡£
SQLÀïÃæÊµÏÖ±éÀúÊý¾Ý¼¯²»·½±ã£¬²»ÏëÓÃÓα꣬ÓÚÊDzÉÓÃÁËÒÔÏ·½Ê½¡£¾ßÌå´æ´¢¹ý³ÌÈçÏ£º
USE [NbShop] GO /****** Object: StoredProcedure [dbo].[CreateLogTable]
Script Date: 07/02/2011 12:54:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWQ -- Create date: 2011-6-29 -- Description: ´´½¨ÈÕÖ¾±í£¨ÃüÃû¹æÔò£º±íÃû+_Log£© -- ============================================= ALTER PROCEDURE [dbo].[CreateLogTable] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here ----------´´½¨ÈÕÖ¾±í------------ declare @rows int declare @n int declare @tableName varchar(100) select @n=1 SELECT name INTO [#tempTables] FROM sys.sysobjects WHERE
(xtype = 'U ') AND (name NOT IN ('sysdiagrams', 'T_BasicTime',
'T_Attribute', 'T_AttributeType', 'T_BasicTime', 'T_City','T_CompeteForMeasu',
'T_DocumentTypeRestrictions', 'T_FormRelevance', 'T_HistroyShopAction',
'T_Notice', 'T_NoticeReceive', 'T_Organize', 'T_OrgType',
'T_Province', 'T_Role', 'T_RptShopStatus', 'T_UploadFile', 'T_UrlPrint'))
AND (name NOT LIKE '%flow%') AND (name NOT LIKE '%Control%') AND
(name NOT LIKE '%Menu%') AND (name NOT LIKE '%Node%') AND (name NOT LIKE '%Log%')
AND (name NOT LIKE '%Event%') AND (name NOT LIKE '%Object%') AND
(name NOT LIKE '%Process%') AND (name NOT LIKE '%ShopStatus%') AND (name NOT LIKE '%Task%')
AND (name NOT LIKE '%ThirdParty%') AND (name NOT LIKE '%User%')
AND (name NOT LIKE '%order%') Select * from #tempTables Select name into #tempCurrent from #tempTables Delete from #tempCurrent select @rows = @@rowcount while @n <= @rows begin set @tableName=(Select top 1 name from #tempTables Where name not in (select name from #tempCurrent)) if(@tableName is not null) begin insert into #tempCurrent values(@tableName) if object_id(@tableName+'_Log') is not null begin print '±í'+ @tableName +'ÒÑ´æÔÚ£¬½ö×öÊý¾Ý¸üд¦Àí' exec ('INSERT INTO'+ @tableName +'_Log SELECT * FROM '+@tableName) end else begin exec ('SELECT * Into '+@tableName+'_Log FROM '+@tableName) print '±í'+ @tableName +'´´½¨³É¹¦' exec ('alter table '+@tableName+'_Log add LogCreateDate datetime') exec ('alter table '+@tableName+'_Log add LogDefaultFlag int') exec ('alter table '+@tableName+'_Log add LogPTID varchar(32)') ---- if col_length( @tableName+' ', 'LogCreateDate ') is not null ---- begin ---- exec ('ALTER TABLE '+@tableName+' DROP COLUMN LogCreateDate') ---- print 'ɾ³ý'+@tableName+'µÄÁÐLogCreateDate³É¹¦' ---- end ---- if(@tableName not in ('T_Shop','T_MeasurementAddress','
T_TurnAround','T_IrisInstrumentHistory','T_ChainTurnApplication','T_TrainingNotice')) ---- begin ---- if col_length( @tableName+' ', 'CreateDate ') is not null ---- begin ---- exec ('ALTER TABLE '+@tableName+' DROP COLUMN CreateDate ') ---- print 'ɾ³ý'+@tableName+'µÄÁÐCreateDate³É¹¦' ---- end ---- end end end select @n = @n + 1 end drop table #tempCurrent drop table #tempTables -------------------´´½¨ÈÕÖ¾±í------------------------------ END |
2£©É¾³ýÈÕÖ¾±í
ÔÚ¿ª·¢¹ý³ÌÖУ¬ÄÑÃâ»á¶Ô×ֶνøÐиü¸Ä¡£ÓÚÊÇɾ³ýµÄ´æ´¢¹ý³ÌÒ²µÃÓС£¾ßÌå´úÂëÈçÏ£º
USE [NbShop] GO /****** Object: StoredProcedure [dbo].[DropLogTable] Script Date: 07/02/2011 12:54:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWQ -- Create date: 2011-6-29 -- Description: ɾ³ýÈÕÖ¾±í£¨_log£© -- ============================================= ALTER PROCEDURE [dbo].[DropLogTable] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -------------------ɾ³ýÈÕÖ¾±í------------------------------ declare @rowsIndex int declare @nIndex int declare @LogTableName varchar(100) select @nIndex=1 SELECT name INTO #LogtempTables FROM sys.sysobjects WHERE (xtype = 'U ') AND (name like '%\_log' escape '\') Select * from #LogtempTables Select name into #LogtempCurrent from #LogtempTables Delete from #LogtempCurrent select @rowsIndex = (Select count(*) from #LogtempTables) print @rowsIndex while @nIndex <= @rowsIndex begin set @LogTableName=(Select top 1 name from #LogtempTables Where name not in (select name from #LogtempCurrent)) if(@LogTableName IS NOT NULL) begin insert into #LogtempCurrent values(@LogTableName) print 'Drop table '+@LogTableName exec ('Drop table '+@LogTableName) print 'ɾ³ý'+@LogTableName+'³É¹¦' end select @nIndex = @nIndex + 1 end Drop table #LogtempTables Drop table #LogtempCurrent SELECT name FROM sys.sysobjects WHERE (xtype = 'U ') AND (name like '%\_log' escape '\') -------------------ɾ³ýÈÕÖ¾±í------------------------------ END |
ÒÔÉÏÓï¾äÖµµÃ×¢ÒâµÄÊÇÔÚ²éÕÒÒÔ¡°_Log¡±½áβµÄ±íÃûµÄËÑË÷Ìõ¼þ£¬ÐèÒª¼ÓÉÏ¡°escape '\'¡±¡£
3£©¼Ç¼ÈÕÖ¾
ÈÕÖ¾±íÓÐÁË£¬»¹µÃ¼Ç¼ÈÕ־Ĩ¡£ÎªÃ¿¸ö±íд¸ö´æ´¢¹ý³Ì»á¹ýÓÚ·±Ëö£¬¶øÇҸ͝Á˾͵øú×ŸĶ¯¡£¾ÍÊÇÂëũҲ³Ô²»Ïû¡£ÓÚÊÇÓÐÁËÒÔÏ´洢¹ý³Ì£¬¸Ã´æ´¢¹ý³Ì¶¨ÒåÁË7¸ö²ÎÊý£¬ÔÊÐí´«Èë´æ´¢¹ý³Ì¡¢±íÃû¡¢WhereÌõ¼þµÈ¡£¾ßÌåÈçÏ£º
USE [NbShop] GO /****** Object: StoredProcedure [dbo].[RecordLog] Script Date: 07/02/2011 12:54:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: LWQ -- Create date: 2011-6-29 -- Description: ¼Ç¼ÈÕÖ¾ -- ============================================= ALTER PROCEDURE [dbo].[RecordLog] @ObjectID varchar(32), ---ÃŵêID @TableName varchar(100), ---±íÃû @PTID varchar(32), ---PTID£¨½×¶ÎID£©£¬¿ÉÑ¡ @PhasesID varchar(32), ---½×¶ÎID£¬¿ÉÑ¡ @ProcedureName varchar(200), ---´æ´¢¹ý³ÌÃû³Æ£¬¿ÉÑ¡ @InsertSQLWhere nvarchar(1000), ---Ö÷±íµÄɸѡÌõ¼þ @UpdateSQLWhere nvarchar(1000) ---Ö÷±íµÄɸѡÌõ¼þ AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF (@ProcedureName is not NULL) BEGIN exec ('exec('+@ProcedureName+''''+@ObjectID+''','''+@PTID+''','''+@PhasesID+''')') END ELSE IF(@InsertSQLWhere IS NOT NULL) BEGIN IF(@UpdateSQLWhere IS NOT NULL) Begin exec('Update '+ @tableName +'_Log Set LogDefaultFlag=0 ' +@UpdateSQLWhere) End Else Begin declare @UpdateExecSQL nvarchar(2000) Select @UpdateExecSQL='Update '+ @tableName +'_Log Set LogDefaultFlag=0 Where ' --------------ÅжÏÊÇ·ñ´æÔÚObjectIDÁУ¨ÃŵêID£©----------------------------- if @ObjectID IS NOT NULL AND col_length( @tableName+' ', 'ObjectID ') is not null BEGIN Select @UpdateExecSQL=@UpdateExecSQL+' ObjectID='''+@ObjectID+''' ' END Else Begin Select @UpdateExecSQL=@UpdateExecSQL+' ShopID='''+@ObjectID+''' ' END exec(@UpdateExecSQL) END exec('INSERT INTO '+
@tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM ' + @tableName+' '+@InsertSQLWhere) END ELSE BEGIN declare @InsertSQL nvarchar(2000) declare @UpdateSQL nvarchar(2000) Declare @WhereCount int Select @WhereCount=0 Select @UpdateSQL='Update '+@tableName+'_Log Set LogDefaultFlag=0 ' select @InsertSQL='INSERT INTO '+
@tableName +'_Log SELECT *,getdate(),1,'''+@PTID+''' FROM ' + @tableName; --------------ÅжÏÊÇ·ñ´æÔÚDefaultFlagÁÐ------------------------- if col_length( @tableName+' ', 'DefaultFlag ') is not null BEGIN select @InsertSQL=@InsertSQL+' Where DefaultFlag=1 ' Select @WhereCount=@WhereCount+1 END --------------ÅжÏÊÇ·ñ´æÔÚObjectIDÁУ¨ÃŵêID£©----------------------------- if @ObjectID IS NOT NULL AND col_length( @tableName+' ', 'ObjectID ') is not null BEGIN IF(@WhereCount>0) BEGIN select @InsertSQL=@InsertSQL+' AND ObjectID='''+@ObjectID+''' ' Select @UpdateSQL=@UpdateSQL+' AND ObjectID='''+@ObjectID+''' ' END ELSE BEGIN select @InsertSQL=@InsertSQL+' WHERE ObjectID='''+@ObjectID+''' ' Select @UpdateSQL=@UpdateSQL+' WHERE ObjectID='''+@ObjectID+''' ' END Select @WhereCount=@WhereCount+1 END --------------ÅжÏÊÇ·ñ´æÔÚShopIDÁУ¨ÃŵêID£©----------------------------- if @ObjectID IS NOT NULL AND col_length( @tableName+' ', 'ShopID ') is not null BEGIN IF(@WhereCount>0) BEGIN select @InsertSQL=@InsertSQL+' AND ShopID='''+@ObjectID+''' ' Select @UpdateSQL=@UpdateSQL+' AND ShopID='''+@ObjectID+''' ' END ELSE BEGIN select @InsertSQL=@InsertSQL+' WHERE ShopID='''+@ObjectID+''' ' Select @UpdateSQL=@UpdateSQL+' WHERE ShopID='''+@ObjectID+''' ' END Select @WhereCount=@WhereCount+1 END --------------ÅжÏÊÇ·ñ´æÔÚPTIDÁУ¨Ä£°æID£©----------------------------- if @PTID IS NOT NULL AND col_length( @tableName+' ', 'PTID ') is not null BEGIN IF(@WhereCount>0) BEGIN select @InsertSQL=@InsertSQL+' AND PTID='''+@PTID+''' ' END ELSE BEGIN select @InsertSQL=@InsertSQL+' WHERE PTID='''+@PTID+''' ' END Select @WhereCount=@WhereCount+1 END --------------ÅжÏÊÇ·ñ´æÔÚPhasesIDÁУ¨½×¶ÎID£©----------------------------- if @PhasesID IS NOT NULL AND col_length( @tableName+' ', 'PhasesID ') is not null BEGIN IF(@WhereCount>0) BEGIN select @InsertSQL=@InsertSQL+' AND PhasesID='''+@PhasesID+''' ' END ELSE BEGIN select @InsertSQL=@InsertSQL+' WHERE PhasesID='''+@PhasesID+''' ' END Select @WhereCount=@WhereCount+1 END print @UpdateSQL exec (@UpdateSQL) print @InsertSQL Exec (@InsertSQL) END END |
´æ´¢¹ý³ÌÓÐÁË£¬ÔÙÅä¸öXML£¬¸ù¾Ý²ÎÊý°ÑTableNameÅä½øÈ¥£¬ÔÙ¼Óµã¿ÉÅäµÄ×Ô¶¨ÒåÊôÐÔʲôµÄ£¬ÀýÈ磺
<Control Display="½âÔ¼ÉêÇë"> <QueryStrings> <QueryString QueryName="PEId">D80E55971198454F97F7EBFE89D239DC</QueryString> </QueryStrings> <Url><![CDATA[/FormServerTemplates/ChainsReleaseForm.aspx]]></Url> <SQlTableName>T_ChainsReleaseForm</SQlTableName> <SQlTableName SQlWhere=" Where CRFID=
(Select top 1 CRFID from T_ChainsReleaseForm Where ShopID={@ShopID@})">T_GeneralFromTable</SQlTableName> </Control> <Control Display="¿Í»§ÐÅÏ¢±í"> <QueryStrings> <QueryString QueryName="PEId">E515165457C5493DA605D4E66416A685</QueryString> <QueryString QueryName="PEId">F9D6E25D978D4E5DB061AE33D68EE279</QueryString> <QueryString QueryName="PEId">D9B9D05380EF4F11B2D2A74D0684DF4B</QueryString> <QueryString QueryName="PEId">45C2B486EB7A463E94B3D55D48DB4A74</QueryString> <QueryString QueryName="PEId">509B5BB3A3B14912ACD633F28A6C91A1</QueryString> <QueryString QueryName="PEId">0CFE53A2A3BB4D6A891B34AA43B0FAC7</QueryString> <QueryString QueryName="PEId">70247883D6414746848E0CE22F06A3F3</QueryString> <QueryString QueryName="PEId">C1E2AD7DFC674DC2AA8434763D4DA0A3</QueryString> <QueryString QueryName="PEId">EE895BBB5B2D43179B196F753ACADCC9</QueryString> </QueryStrings> <Url><![CDATA[/FormServerTemplates/AddShopInfo.aspx]]></Url> <SQlTableName>T_Shop</SQlTableName> <SQlTableName>T_Shopkeeper</SQlTableName> <SQlTableName>T_Acreage</SQlTableName> <SQlTableName>T_BusinessDistrict</SQlTableName> <SQlTableName>T_Compete</SQlTableName> <SQlTableName>T_SupportingFacility</SQlTableName> </Control> |
ÕâÑù£¬¾ÍÒ»ÀÍÓÀÒÝÁË¡£
|