1. Home
  2. Data
  3. Record Storage Services
  4. Deleted Tables
  5. Table – DeleteRequestValidation
Searching...

Table – DeleteRequestValidation

Contents

Purpose

Controls the process of confirming an Instance record deletion request and the transfer of data from the active core tables to the equivalent deleted tables.

Definition

CREATE TABLE [dbo].[DeleteRequestValidation](
	[RequestGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[InstanceGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[Hierarchy] [VARCHAR](500) NOT NULL,
	[InstanceName] [VARCHAR](500) NOT NULL,
	[InstanceStatus] [VARCHAR](50) NOT NULL,
	[ConceptGUID] [UNIQUEIDENTIFIER] NOT NULL,
	[DeleteTSQL] [VARCHAR](200) NULL,
	[CanDelete] [CHAR](1) NULL,
 CONSTRAINT [PK_DeleteRequestValidation] PRIMARY KEY CLUSTERED 
(
	[RequestGUID] ASC,
	[InstanceGUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Column Definition

RequestGUID groups all of the records encompassed by this deletion request. This will include the Instance record and ALL of its descendent Instance records.

InstanceGUID identifies the Instance record that is being considered for deletion.

Hierarchy tracks the position of the record in the hierarchy of the database.

InstanceStatus indicates the current status of the record.

ConceptGUID indicates the type of record.

DeleteTSQL indicates the name of an optional TSQL Stored Procedure that will determine whether the record can be deleted.

CanDelete indicates whether the record can be deleted. By default, this is set to ‘Y’ and is only set to ‘N’ if the user can’t delete the record – in which case the entire request is rolled back.

Key Functionality

When the user requests the deletion of an Instance record a call is made to the GI_RequestDelete TSQL Stored Procedure. This creates the request and adds the @CurrentInstanceGUID and all of its descendants to the DeleteRequestValidation table. The Instances included in the request are then sanity checked to determine whether the user has permission to delete the instance. If any check fails, then the entire delete process is failed. Assuming the sanity checks pass then control passes back to the Core API to run any required detailed “DeleteTSQL” against each Instance.

CREATE PROCEDURE [dbo].[GI_RequestDelete] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER
		
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDelete - Invalid session', 1, NULL
		RAISERROR ('DB.GI_RequestDelete - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END
		
	BEGIN TRY
		
		-- Copy the instance and its children to the DeleteRequestValidation table...
		DECLARE @Hierarchy AS VARCHAR(500)
		SELECT @Hierarchy = Hierarchy FROM dbo.Instances WITH (NOLOCK) WHERE InstanceGUID = @CurrentInstanceGUID
		SET @Hierarchy = @Hierarchy + CONVERT(VARCHAR(40), @CurrentInstanceGUID) + '%'

		INSERT INTO DeleteRequestValidation (RequestGUID, InstanceGUID, Hierarchy, InstanceName, InstanceStatus, ConceptGUID, DeleteTSQL, CanDelete)
		SELECT @CurrentInstanceGUID, I.InstanceGUID, I.Hierarchy, I.InstanceName, I.InstanceStatus, I.ConceptGUID, '', 'Y'
		FROM dbo.Instances I WITH (NOLOCK, FORCESEEK)
		WHERE I.InstanceGUID = @CurrentInstanceGUID OR I.Hierarchy LIKE @Hierarchy
		
		UPDATE dbo.Instances SET LockedBySessionGUID = @SessionGUID, LockExpires = DATEADD(MINUTE, 20, SYSUTCDATETIME())
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		-- Begin a basic check to see whether this user is allowed to delete each row...
		DECLARE @MyInstanceGUID AS UNIQUEIDENTIFIER
		DECLARE @MyStatus AS VARCHAR(200)
		DECLARE @MyConceptGUID AS UNIQUEIDENTIFIER

		DECLARE TargetInstances CURSOR FOR 
		SELECT InstanceGUID, InstanceStatus, ConceptGUID
		FROM DeleteRequestValidation WITH (NOLOCK)
		WHERE RequestGUID = @CurrentInstanceGUID

		OPEN TargetInstances

		FETCH NEXT FROM TargetInstances INTO @MyInstanceGUID, @MyStatus, @MyConceptGUID

		WHILE @@FETCH_STATUS = 0
		BEGIN

			-- Does the user even have access to this instance?
			EXECUTE @ReturnStatus = dbo.GI_ValidateSaveAccess @SessionGUID, @UserGUID, @MyInstanceGUID
			IF @ReturnStatus <> 0
			BEGIN
				UPDATE DeleteRequestValidation SET CanDelete = 'N', InstanceName = 'No access', InstanceStatus = 'No access', Hierarchy = 'No access'
				WHERE RequestGUID = @CurrentInstanceGUID AND InstanceGUID = @CurrentInstanceGUID
			END

			FETCH NEXT FROM TargetInstances INTO  @MyInstanceGUID, @MyStatus, @MyConceptGUID

		END

		CLOSE TargetInstances;
		DEALLOCATE TargetInstances;

		-- Now check out the basic form permissions...
		DECLARE @Delete AS VARCHAR(5) = ''
		DECLARE @DeleteTSQL AS VARCHAR (200) = ''
		DECLARE @Role AS VARCHAR (200) = ''
		DECLARE @State AS VARCHAR (200) = ''
		DECLARE @Priority AS INTEGER = 0

		-- Begin a detailed check to see if this user can delete based purely on state and role...
		DECLARE ConceptStates CURSOR FOR 
		SELECT DISTINCT DRV.InstanceStatus, DRV.ConceptGUID
		FROM DeleteRequestValidation DRV WITH (NOLOCK)
		WHERE RequestGUID = @CurrentInstanceGUID
		OPEN ConceptStates

		FETCH NEXT FROM ConceptStates INTO @MyStatus, @MyConceptGUID

		WHILE @@FETCH_STATUS = 0
		BEGIN

			DECLARE FormPermissions CURSOR FOR 
			SELECT DISTINCT FP.[Delete], FP.DeleteTSQL, FP.[Role], FP.[State], FP.[Priority]
			FROM vw_ZSO_FormPermission FP WITH (NOLOCK)
			WHERE FP.ParentGUID = @MyConceptGUID
			ORDER BY [Priority] DESC
			OPEN FormPermissions

			FETCH NEXT FROM FormPermissions INTO @Delete, @DeleteTSQL, @Role, @State, @Priority
			DECLARE @Matched AS INTEGER = 0

			WHILE @@FETCH_STATUS = 0 AND @Matched = 0
			BEGIN

				DECLARE @MatchState AS INTEGER = 0
				DECLARE @MatchRole AS INTEGER = 0

				-- Does this form permission match on State?
				IF @State IS NULL
				BEGIN
					SET @MatchState = 1 -- Nothing to restrict
				END
				ELSE
				BEGIN
					IF @MyStatus = @State 
					BEGIN
						SET @MatchState = 1
					END
				END

				IF @MatchState = 1
				BEGIN
					-- State is good so does this form permission also match on Role?
					IF @Role IS NULL
					BEGIN
						SET @MatchRole = 1 -- Nothing to restrict
					END
					ELSE
					BEGIN

						-- Does the current user have a matching role?
						DECLARE @UserRole AS VARCHAR(500)
						DECLARE UserRoles CURSOR FOR 
						SELECT DISTINCT I.InstanceName
						FROM dbo.UserRoles R WITH (NOLOCK)
						INNER JOIN dbo.Instances I WITH (NOLOCK) ON I.InstanceGUID = R.RoleGUID
						WHERE R.UserGUID = @UserGUID
						OPEN UserRoles

						FETCH NEXT FROM UserRoles INTO @UserRole

						WHILE @@FETCH_STATUS = 0
						BEGIN
							IF @UserRole = @Role
							BEGIN
								SET @MatchRole = 1
							END
							FETCH NEXT FROM UserRoles INTO  @UserRole
						END

						CLOSE UserRoles;
						DEALLOCATE UserRoles;

					END

				END

				-- Is this the highest priority matching form permission?
				IF @MatchState = 1 AND @MatchRole = 1
				BEGIN

					-- Is the form permission No?
					IF @Delete = 'No'
					BEGIN
						UPDATE DeleteRequestValidation SET CanDelete = 'N' 
						WHERE RequestGUID = @CurrentInstanceGUID AND ConceptGUID = @MyConceptGUID AND InstanceStatus = @MyStatus
					END

					-- Is the form permission TSQL?
					IF @Delete = 'TSQL'
					BEGIN

						-- Defer judgement based on the TSQL applied per instance...
						UPDATE DeleteRequestValidation SET DeleteTSQL = @DeleteTSQL 
						WHERE RequestGUID = @CurrentInstanceGUID AND ConceptGUID = @MyConceptGUID AND InstanceStatus = @MyStatus

					END

					SET @Matched = 1

				END
				ELSE
				BEGIN
					FETCH NEXT FROM FormPermissions INTO  @Delete, @DeleteTSQL, @Role, @State, @Priority
				END

			END

			CLOSE FormPermissions;
			DEALLOCATE FormPermissions;

			FETCH NEXT FROM ConceptStates INTO @MyStatus, @MyConceptGUID

		END

		CLOSE ConceptStates;
		DEALLOCATE ConceptStates;

		SELECT * FROM DeleteRequestValidation 
		WHERE RequestGUID = @CurrentInstanceGUID AND (CanDelete = 'N' OR DeleteTSQL <> '')

		RETURN 0
				
	END TRY
	BEGIN CATCH
        DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDelete - Unknown error requesting deletion of instance data', 1, @ErrorMessage
		RAISERROR ('DB.GI_RequestDelete - Unknown error requesting deletion of instance data.', 16, 1)
		RETURN 1
	END CATCH

END

If the deletion request is invalid, then a call is made to the GI_RequestDeleteCancel TSQL Stored Procedure which simply removes the request from the DeleteRequestValidation table.

CREATE PROCEDURE [dbo].[GI_RequestDeleteCancel] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER
		
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDeleteCancel - Invalid session', 1, NULL
		RAISERROR ('DB.GI_RequestDeleteCancel - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END
		
	BEGIN TRY


		UPDATE dbo.Instances SET LockedBySessionGUID = NULL, LockExpires = NULL
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM DeleteRequestValidation 
		WHERE RequestGUID = @CurrentInstanceGUID

		RETURN 0
				
	END TRY
	BEGIN CATCH
        DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDeleteCancel - Unknown error requesting deletion of instance data', 1, @ErrorMessage
		RAISERROR ('DB.GI_RequestDeleteCancel - Unknown error requesting deletion of instance data.', 16, 1)
		RETURN 1
	END CATCH

END

If all of the conditions for the request are met and the user has permission to complete the deletion then the GI_RequestDeleteCommit TSQL Stored Procedure is used to move all of the deleted records from their active core tables to the equivalent deleted tables.

CREATE PROCEDURE [dbo].[GI_RequestDeleteCommit] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER,
	@TargetInstanceGUID AS UNIQUEIDENTIFIER
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER
		
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		
		-- Log and raise the error...
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDeleteCommit - Invalid session', 1, NULL
		RAISERROR ('DB.GI_RequestDeleteCommit - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
			
	END
		
	BEGIN TRY

		DECLARE @AchievedBy AS VARCHAR(100)
		SELECT @AchievedBy = UserName
		FROM dbo.LU_ZWO_User WITH (NOLOCK)
		WHERE UserGUID = @UserGUID

		INSERT INTO dbo.InstanceMilestones
		(
		    InstanceGUID,
		    AchievedOn,
		    AchievedBy,
		    Milestone
		)
		SELECT Instanceguid, SYSUTCDATETIME(), @AchievedBy, 'Deleted'
		FROM dbo.Instances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		-- Move logically deleted records to the deleted tables...
		DELETE FROM dbo.DeletedSelectedInstances
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedSelectedInstances (InstanceGUID, SelectedInstanceGUID)
		SELECT InstanceGUID, SelectedInstanceGUID
		FROM dbo.SelectedInstances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.DeletedInstanceAccess
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedInstanceAccess (InstanceGUID, UserGroupGUID)
		SELECT InstanceGUID, UserGroupGUID
		FROM dbo.InstanceAccess WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.DeletedInstances
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedInstances (RequestGUID, InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID, OldPrimaryKey,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, DeletedByGUID, DeletedOn, CreatedByGUID, CreatedOn, LastUpdated, LastUpdatedByGUID, CorrelationGUID)
		SELECT @CurrentInstanceGUID, InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID, OldPrimaryKey,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, @UserGUID, SYSUTCDATETIME(), CreatedByGUID, CreatedOn, LastUpdated, LastUpdatedByGUID, CorrelationGUID
		FROM dbo.Instances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.DeletedInstanceEvents
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedInstanceEvents (InstanceGUID, LoggedOn, [Event])
		SELECT InstanceGUID, LoggedOn, [Event]
		FROM dbo.InstanceEvents WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.DeletedInstanceHistory 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedInstanceHistory (InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CorrelationGUID)
		SELECT InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CorrelationGUID
		FROM dbo.InstanceHistory WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.DeletedInstanceMilestones 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		INSERT INTO dbo.DeletedInstanceMilestones (InstanceGUID, AchievedBy, AchievedOn, Milestone)
		SELECT InstanceGUID, AchievedBy, AchievedOn, Milestone
		FROM dbo.InstanceMilestones WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.InstanceAccess
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.InstanceHistory
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.InstanceMilestones
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		DELETE FROM dbo.Instances
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeleteRequestValidation WITH (NOLOCK) WHERE RequestGUID = @CurrentInstanceGUID)

		-- Clean up the delete request validation table...
		DELETE FROM DeleteRequestValidation 
		WHERE RequestGUID = @CurrentInstanceGUID

		RETURN 0
				
	END TRY
	BEGIN CATCH
        DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_RequestDeleteCommit - Unknown error requesting deletion of instance data', 1, @ErrorMessage
		RAISERROR ('DB.GI_RequestDeleteCommit - Unknown error requesting deletion of instance data.', 16, 1)
		RETURN 1
	END CATCH

END

After an Instance has been “deleted” it can be restored by the user through the GI_UndeleteInstance TSQL Stored procedure. This procedure simply moves the data back from the deleted tables to their equivalent active core tables.

CREATE PROCEDURE [dbo].[GI_UndeleteInstance] 
(
	@SessionGUID AS UNIQUEIDENTIFIER,
	@UserGUID AS UNIQUEIDENTIFIER,
	@CurrentInstanceGUID AS UNIQUEIDENTIFIER, --parentGUID
	@TargetInstanceGUID AS UNIQUEIDENTIFIER --instance actually being restored
)
AS -- DECRYPT
-- WITH ENCRYPTION AS
BEGIN

	DECLARE @ReturnStatus AS INTEGER
		
	EXECUTE @ReturnStatus = dbo.GI_ValidateSession @SessionGUID, @UserGUID
	IF @ReturnStatus <> 0
	BEGIN
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_UndeleteInstance - Invalid session', 1, NULL
		RAISERROR ('DB.GI_UndeleteInstance - Invalid session.', 16, 1)
		RETURN 100 + @ReturnStatus
	END
		
	BEGIN TRY
		

		DELETE FROM dbo.Instances 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.Instances (InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID, OldPrimaryKey,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CreatedByGUID, CreatedOn, LastUpdated, LastUpdatedByGUID, CorrelationGUID)
		SELECT InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID, OldPrimaryKey,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CreatedByGUID, CreatedOn, LastUpdated, LastUpdatedByGUID, CorrelationGUID
		FROM dbo.DeletedInstances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.SelectedInstances
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.SelectedInstances (InstanceGUID, SelectedInstanceGUID)
		SELECT InstanceGUID, SelectedInstanceGUID
		FROM dbo.DeletedSelectedInstances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.DeletedSelectedInstances 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.InstanceAccess
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.InstanceAccess (InstanceGUID, UserGroupGUID)
		SELECT InstanceGUID, UserGroupGUID
		FROM dbo.DeletedInstanceAccess WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.DeletedInstanceAccess 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.InstanceHistory 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.InstanceHistory (InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CorrelationGUID)
		SELECT InstanceGUID, DataVersion, InstanceName, ConceptGUID, Hierarchy, ParentGUID,
			InsertedByGUID, InsertedOn, InstanceStatus, InstanceData, CorrelationGUID
		FROM dbo.DeletedInstanceHistory WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.DeletedInstanceHistory 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.InstanceEvents 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.InstanceEvents (InstanceGUID, LoggedOn, [Event])
		SELECT InstanceGUID, LoggedOn, [Event]
		FROM dbo.DeletedInstanceEvents WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.DeletedInstanceEvents
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.InstanceMilestones 
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		INSERT INTO dbo.InstanceMilestones (InstanceGUID, AchievedOn, AchievedBy, Milestone)
		SELECT InstanceGUID, AchievedOn, AchievedBy, Milestone
		FROM dbo.DeletedInstanceMilestones WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DELETE FROM dbo.DeletedInstanceMilestones
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)

		DECLARE @AchievedBy AS VARCHAR(100)
		SELECT @AchievedBy = UserName
		FROM dbo.LU_ZWO_User WITH (NOLOCK)
		WHERE UserGUID = @UserGUID

		INSERT INTO dbo.InstanceMilestones
		(
		    InstanceGUID,
		    AchievedOn,
		    AchievedBy,
		    Milestone
		)
		SELECT Instanceguid, SYSUTCDATETIME(), @AchievedBy, 'Undeleted'
		FROM dbo.Instances WITH (NOLOCK)
		WHERE InstanceGUID IN (SELECT InstanceGUID FROM DeletedInstances WITH (NOLOCK) WHERE RequestGUID = @TargetInstanceGUID)
		
		DELETE FROM dbo.DeletedInstances WHERE RequestGUID = @TargetInstanceGUID

		SELECT I.InstanceGUID, I.InstanceName, I.DataVersion, I.InstanceStatus, I.DeletedOn, U.InstanceName AS DeletedBy
		FROM dbo.DeletedInstances I WITH (NOLOCK)
		INNER JOIN dbo.Instances U WITH (NOLOCK) ON U.InstanceGUID = I.DeletedByGUID
		WHERE I.ParentGUID = @CurrentInstanceGUID
		ORDER BY I.InstanceName

		RETURN 0
				
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage AS VARCHAR(1000) = ERROR_MESSAGE()
		EXECUTE @ReturnStatus = dbo.GI_LogEvent @UserGUID, 'DB.GI_UndeleteInstance - Unknown error restoring instance data', 1, @ErrorMessage
		RAISERROR ('DB.GI_UndeleteInstance - Unknown error restoring instance data.', 16, 1)
		RETURN 1
	END CATCH

END

Updated on November 24, 2022

Was this article helpful?

Yes No

About The Author

Tony Nicholls

Building application systems with 100% of the desired requirements with only a fraction of the cost, time, and risk. This is the problem Tony has been working to solve for more than 35 years. Starting his career in the UK military sector, he has been developing highly secure, scalable, and robust applications since 1985. He has been relentless in finding innovative ways to build new applications. This has come to a pinnacle with trellispark, which eliminates 90% of the costs and effort of application development without compromising on quality, scalability, or security. Tony leads our innovation center of excellence and development teams. His Data Agnostic Services vision has sparked a movement that shakes the traditional assumptions of software development.

Related Articles

Need Support?
Can’t find the answer you’re looking for? Don’t worry we’re here to help!
Contact Support

Leave a Comment