MS SQL Archive Table - Restore

Hi,

I have a table named t_Devices which has an ID column as an Identity column. The ID column is used to as the device ID and primary key. When a user deletes a device a trigger runs to insert the record into a archive table t_DevicesArchive.

What I now need to do is create a stored procedure to restore the archive record from t_DevicesArchive to t_Devices. I have created an procedure named p_DeviceArchive_Restore but the user must have the ALTER permission on the table, which I do not want to set.

Is there a better way to do this, but I need to have an identity column for the Device ID? Or should I just execute the stored procedure as the owner...

Please see the code below:

Device Table:
CREATE TABLE [dbo].[tbl_Devices](
	[Device_ID] [int] IDENTITY(1,1) NOT NULL,
	[Device_Name] [varchar](30) NOT NULL,
	[Device_LocationID] [int] NOT NULL,
	[Device_Location2] [varchar](30) NULL,
	[Device_MACAddress] [varchar](20) NULL,
	[Device_IPAddress] [varchar](15) NULL,
	[Device_Active] [bit] NOT NULL,
	[Device_CreateDateTime] [datetime] NOT NULL,
	[Device_ModifiedDateTime] [datetime] NULL,
	[Device_Manufacturer] [varchar](50) NULL,
	[Device_Model] [varchar](50) NULL,
	[Device_OS] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_Devices] PRIMARY KEY CLUSTERED )

Open in new window



Device Archive Table:
CREATE TABLE [dbo].[t_DevicesArchive](
	[Device_ID] [int] NOT NULL,
	[Device_Name] [varchar](30) NOT NULL,
	[Device_LocationID] [int] NOT NULL,
	[Device_Location2] [varchar](30) NULL,
	[Device_MACAddress] [varchar](20) NULL,
	[Device_IPAddress] [varchar](15) NULL,
	[Device_Active] [bit] NOT NULL,
	[Device_CreateDateTime] [datetime] NOT NULL,
	[Device_ModifiedDateTime] [datetime] NULL,
	[Device_Manufacturer] [varchar](50) NULL,
	[Device_Model] [varchar](50) NULL,
	[Device_OS] [varchar](50) NULL
) ON [PRIMARY]

Open in new window


Delete trigger:
CREATE TRIGGER [dbo].[tr_Devices_Delete]
ON [dbo].[t_Devices]
AFTER DELETE AS

BEGIN

	INSERT INTO dbo.t_DevicesArchive
	SELECT * FROM Deleted

Open in new window


Restore procedure:
CREATE PROCEDURE p_DevicesArchive_Restore
(
	@Device_ID INT
)

AS

BEGIN
	
	SET IDENTITY_INSERT dbo.t_Devices ON;
	
	INSERT INTO dbo.t_Devices
		(
			Device_ID,
			Device_Name,
			Device_LocationID,
			Device_Location2,
			Device_MACAddress,
			Device_IPAddress,	
			Device_Active,
			Device_CreateDateTime,
			Device_ModifiedDateTime,
			Device_Manufacturer,
			Device_Model,
			Device_OS	
		)
	SELECT
		Device_ID,
		Device_Name,
		Device_LocationID,
		Device_Location2,
		Device_MACAddress,
		Device_IPAddress,	
		Device_Active,
		Device_CreateDateTime,
		Device_ModifiedDateTime,
		Device_Manufacturer,
		Device_Model,
		Device_OS
	FROM
		dbo.t_DevicesArchive
	WHERE
		Device_ID = @Device_ID;
		
	SET IDENTITY_INSERT dbo.t_Devices OFF;
	
	SELECT @@ERROR 
	
END

Open in new window

spen_langAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Use EXECUTE AS OWNER.

Caveat: Your model is flawed. Consider deleting a device, restoring it, deleting it. This will lead to two rows in your history table. Thus a subsequent restore will fail.
spen_langAuthor Commented:
Thanks ste5an I noticed this just after I posted the question...

I have updated the stored procedure to delete the record from the archive if the restore succeeds as the record is now current and not archive.

So I presume the SET IDENTITY_INSERT dbo.t_Devices ON; is the best method to use and set the line WITH EXECUTE AS OWNER

ALTER PROCEDURE [dbo].[p_DevicesArchive_Restore]
(
	@Device_ID INT
)
WITH EXECUTE AS OWNER
AS

BEGIN
	
	SET IDENTITY_INSERT dbo.t_Devices ON;
	
	INSERT INTO dbo.t_Devices
		(
			Device_ID,
			Device_Name,
			Device_LocationID,
			Device_Location2,
			Device_MACAddress,
			Device_IPAddress,	
			Device_Active,
			Device_CreateDateTime,
			Device_ModifiedDateTime,
			Device_Manufacturer,
			Device_Model,
			Device_OS	
		)
	SELECT
		Device_ID,
		Device_Name,
		Device_LocationID,
		Device_Location2,
		Device_MACAddress,
		Device_IPAddress,	
		Device_Active,
		Device_CreateDateTime,
		Device_ModifiedDateTime,
		Device_Manufacturer,
		Device_Model,
		Device_OS
	FROM
		dbo.t_DevicesArchive
	WHERE
		Device_ID = @Device_ID;
		
	SET IDENTITY_INSERT dbo.tbl_Devices OFF;
	
	IF @@ERROR = 0
		BEGIN
		
			DELETE FROM dbo.t_DevicesArchive
			WHERE
				Device_ID = @Device_ID 
		
		END
	
	SELECT @@ERROR 
	
END

Open in new window

ste5anSenior DeveloperCommented:
Don't use the IF @@ERROR construct. Use an explicit transaction instead. E.g. something like this

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE PROCEDURE dbo.p_RestoreDevice ( @DeviceID INT )
AS
    BEGIN TRY                                                         
        SET NOCOUNT ON;
        SET XACT_ABORT ON;

	-- Constants.
        DECLARE @NO_ERROR INT = 0;
        DECLARE @GENERIC_ERROR INT = 55555;

	-- Variables.        
        DECLARE @Result INT = @NO_ERROR;

	-- Use explicit transaction.
        BEGIN TRANSACTION;

        SET IDENTITY_INSERT dbo.t_Devices ON;
	
        INSERT  INTO dbo.t_Devices
                ( Device_ID ,
                  Device_Name ,
                  Device_LocationID ,
                  Device_Location2 ,
                  Device_MACAddress ,
                  Device_IPAddress ,
                  Device_Active ,
                  Device_CreateDateTime ,
                  Device_ModifiedDateTime ,
                  Device_Manufacturer ,
                  Device_Model ,
                  Device_OS	
		        )
                SELECT  Device_ID ,
                        Device_Name ,
                        Device_LocationID ,
                        Device_Location2 ,
                        Device_MACAddress ,
                        Device_IPAddress ,
                        Device_Active ,
                        Device_CreateDateTime ,
                        Device_ModifiedDateTime ,
                        Device_Manufacturer ,
                        Device_Model ,
                        Device_OS
                FROM    dbo.t_DevicesArchive
                WHERE   Device_ID = @Device_ID;
		
        SET IDENTITY_INSERT dbo.tbl_Devices OFF;
	
        DELETE  FROM dbo.t_DevicesArchive
        WHERE   Device_ID = @Device_ID; 
	
	-- Use explicit transaction.
        COMMIT TRANSACTION;
		
        RETURN @Result;
    END TRY  
    BEGIN CATCH
        IF ( @@trancount > 0 )
            ROLLBACK TRANSACTION;
        EXECUTE dbo.p_ThrowError;
        RETURN @GENERIC_ERROR;
    END CATCH;
GO

CREATE PROCEDURE dbo.p_ThrowError
AS
    SET NOCOUNT ON;

    -- Variables.
    DECLARE @ErrorMessage NVARCHAR(2048) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity TINYINT = ERROR_SEVERITY();
    DECLARE @ErrorState TINYINT = ERROR_STATE();
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorProcedure sysname = ERROR_PROCEDURE();
    DECLARE @ErrorLine INT = ERROR_LINE();           

    -- Mark error with leading asterisks to distingush between compiliation and run-time errors.
    IF ( @ErrorMessage NOT LIKE '***%' )
        BEGIN 
            SET @ErrorMessage = '*** ' + COALESCE(QUOTENAME(@ErrorProcedure), '<dynamic SQL>') + ', ' + LTRIM(STR(@ErrorLine)) + '. Error #'
                + LTRIM(STR(@ErrorNumber)) + ': ' + @ErrorMessage;
        END;

    SET @ErrorNumber += 50000;
    THROW @ErrorNumber, @ErrorMessage, @ErrorState;
GO

Open in new window

 

See also Erland's article about Error and Transaction Handling in SQL Server.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
You want to be absolutely sure the row is in the main table before you DELETE it from the archive.  Therefore, use an EXISTS() check before deleting the row.  You can still capture the @@ERROR code from the INSERT and return it from the proc.

Btw, you can't get an error message with ERROR_MESSAGE() outside of a CATCH block.  Thus, you can't use a separate stored proc to retrieve the error info.


ALTER PROCEDURE [dbo].[p_DevicesArchive_Restore]
(
      @Device_ID INT
)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;      
DECLARE @error int;

SET IDENTITY_INSERT dbo.t_Devices ON;

INSERT INTO dbo.t_Devices
      (
            Device_ID,
            Device_Name,
            Device_LocationID,
            Device_Location2,
            Device_MACAddress,
            Device_IPAddress,      
            Device_Active,
            Device_CreateDateTime,
            Device_ModifiedDateTime,
            Device_Manufacturer,
            Device_Model,
            Device_OS      
      )
SELECT
      Device_ID,
      Device_Name,
      Device_LocationID,
      Device_Location2,
      Device_MACAddress,
      Device_IPAddress,      
      Device_Active,
      Device_CreateDateTime,
      Device_ModifiedDateTime,
      Device_Manufacturer,
      Device_Model,
      Device_OS
FROM
      dbo.t_DevicesArchive
WHERE
      Device_ID = @Device_ID;
      
SET @error = @@ERROR
      
SET IDENTITY_INSERT dbo.tbl_Devices OFF;

IF EXISTS(
SELECT 1
FROM dbo.t_DevicesArchive
WHERE Device_ID = @Device_ID;
)
      BEGIN
      
            DELETE FROM dbo.t_DevicesArchive
            WHERE
                  Device_ID = @Device_ID;

      END

RETURN @error
GO --end of proc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Good idea, but the transaction is still necessary.

Otherwise it may fail when having the wrong timing. Cause some other thread may delete the inserted row before the procedure executes the delete.

An explicit transaction will hold a X lock until the row is deleted.
Scott PletcherSenior DBACommented:
Seriously?  You think realistically that some other transaction is going to delete the row I just inserted before I can immediately check that the row exists??  How would it get that identity value to delete??

There's nothing wrong with a transaction here, of course, but I'm not sure that it's realistically required.
ste5anSenior DeveloperCommented:
No, just kidding..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.