Automated Version Management

Comments

• Options

  sc00by Member Posts: 43

  2008-03-06

  Hello,
  
  I haven't loaded this but it does look quite useful =D>
  What I did do though, is take a look at the trigger and other bits and thought that a similar thing we've done here for auditing changes may be a useful enhancement to the trigger and logging table.
  
  We also capture both the user name and the machine they were logged in from - this has been quite useful for tracking down the "culprit" when changes are put in that give unexpected behaviour #-o
  
  These two SQL Server functions can be added into the INSERT statement
  system_user
  host_name()
  
  I'm sure it's pretty obvious what each one returns
  
  Hope this is useful to somebody.
  
  Rob.

  0
• Options

  lyot Member Posts: 202

  2008-03-13

  This is really awesome... :shock:
  
  But in order to avoid 100 versions of 1 object... couldn't it be possible
  to remove versions. With the proper warnings off course.... O:)

  My Nav Blog

  0
• Options

  raphael.fehlmann Member Posts: 3

  2008-04-11

  I also tried the mentiones SQL statements to show the user and it worked.
  Anothe suggestion is to only save entries if the date and time has changed. Since everytime when is just compile an object withour saving. It also adds a version entry with the same time.
  It happend to me that i compiled all object in order to find out if all are compilable and it added a version entry for all objects.
  
  For initializing der version management i was first running a report which just did a modify(False) on all objects.

  0
• Options

  raphael.fehlmann Member Posts: 3

  2008-04-18

  if versions between are removed it doesn't work anymore. Beacuse it counts the version to find the next version no.
  
  It would be better to find the last version and add 1 to find the next version no.
  SELECT TOP(1) @Version = Version + 1 FROM dbo.[Object Version] WHERE [Type] = @Type AND ID = @ID ORDER BY Version DESC

  0
• Options

  tlarson Member Posts: 27

  2008-12-18

  Run this code to perform an initial 'backup' of all objects:
  

  USE [CronusPortal]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:		<Author,,Name>
  -- Create date: <Create Date,,>
  -- Description:	<Description,,>
  -- =============================================
  
  	SET NOCOUNT ON
  
  	DECLARE @NumObjectsSaved int
  	SELECT @NumObjectsSaved = COUNT([Version]) FROM dbo.[Object Version]
  
  	IF @NumObjectsSaved <> 0 BEGIN
  		PRINT N'ERROR: The ''Object Version'' table is not empty.  It must be empty to perform an initial backup';
  	END ELSE BEGIN
  
  		INSERT INTO [Object Version] ([Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
  			[Date], [Time], [Version List], [Version], [Active], [User ID])
  		SELECT o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], o.[BLOB Reference], o.[BLOB Size], 
  			o.[Date], o.[Time], o.[Version List], 1, 1, USER_NAME()
  		FROM dbo.[Object] o
  		WHERE o.[Type] IN (1, 2, 3, 4, 5, 6, 7)
  
  		PRINT N'Objects Backed up to the ''Object Version'' table';
  
  	END;
  

  0
• Options

  tlarson Member Posts: 27

  2008-12-18

  And try using this code for the trigger. It has 2 enhancements:
  
  1. It uses SYSTEM_USER, which seems to do a better job of grabbing the real username
  2. To create a Version number, it finds the highest version number and adds one. That way, if you delete a version, it'll still be able to generate a new number
  

  USE [CronusPortal]
  GO
  /****** Object:  Trigger [dbo].[Object_SaveVersion]    Script Date: 02/22/2008 10:02:07 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  -- =============================================
  -- Author:		<Author,,Name>
  -- Create date: <Create Date,,>
  -- Description:	<Description,,>
  -- =============================================
  CREATE TRIGGER [dbo].[Object_RevertToSavedVersion]
     ON  [dbo].[Object Version] 
     AFTER UPDATE
  AS 
  BEGIN
  	SET NOCOUNT ON
  	DECLARE
  		@Type int,
  		@ID int,
  		@Version int,
  		@Count int
  
  	SELECT @Type = [Type], @ID = [ID] FROM inserted
  	IF @Type IN (1, 2, 3, 4, 5, 6, 7) BEGIN
  
  		UPDATE [Object Version] SET [Active] = 0 WHERE [ID] = @ID AND [Type] = @Type
  
  		SELECT @Version = ISNULL(MAX([Version]),1)+1 FROM dbo.[Object Version] WHERE [Type] = @Type AND ID = @ID
  
  		INSERT INTO [Object Version] ([Type], [ID], [Name], [Modified], [Compiled], [BLOB Reference], [BLOB Size],
  			[Date], [Time], [Version List], [Version], [Active], [User ID])
  		SELECT o.[Type], o.[ID], o.[Name], o.[Modified], o.[Compiled], o.[BLOB Reference], o.[BLOB Size], 
  			o.[Date], o.[Time], o.[Version List], @Version, 1, SYSTEM_USER
  		FROM dbo.[Object] o INNER JOIN inserted i ON i.[Type] = o.[Type] AND i.[ID] = o.[ID]
  
  	END
  END
  
  

  0
• Options

  dgroeser Member Posts: 39

  2009-10-13

  This is very usefull, but i have a question.
  
  i would save the vomplete C/AL Code additional per SQL-Trigger, my idea is the per sql trigger the txt export per handler (com-dll) and import the file per sql trigger too, could your help me??

  0