CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
NoviceDeveloper
NoviceDeveloper

Reputation: 1290

SQL Server stored procedure restore my records if Insert failed

I want to know if there is a way to state rollback the delete if I can not insert.

Please advice.

Something like below.

BEGIN TRAN
Delete from MYTABLE where ID=@ID;

INSERT INTO MYTABLE (ID, NAME)
SELECT @ID, NAME

COMMIT

Upvotes: 4

Views: 7992

Answers (4)

dotjoe
dotjoe

Reputation: 26940

Turn xact_abort on to rollback the transaction on any error.

SET XACT_ABORT ON;
BEGIN TRAN
Delete from MYTABLE where ID=@ID;

INSERT INTO MYTABLE (ID, NAME)
SELECT @ID, NAME

COMMIT TRAN

Upvotes: 3

Adam Anderson
Adam Anderson

Reputation: 508

BEGIN TRAN

Delete from MYTABLE where ID=@ID;

INSERT INTO MYTABLE (ID, NAME)
SELECT @ID, NAME

if @@error = 0 and @@trancount > 0
    commit
else
    rollback

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Here is another way to accomplish what you appear to be trying:

update myTable
set name = @name
where id = @id

Upvotes: 0

marc_s
marc_s

Reputation: 754538

You can put your two statements into a TRY....CATCH block and only commit if both statements succeed:

BEGIN TRANSACTION
BEGIN TRY
    DELETE FROM dbo.MYTABLE WHERE ID=@ID;

    INSERT INTO dbo.MYTABLE (ID, NAME)
       SELECT @ID, NAME

    -- COMMIT only if both DELETE and INSERT worked ....
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    -- ROLLBACK if either DELETE and INSERT failed ....
    ROLLBACK TRANSACTION
END CATCH

Upvotes: 10

Related Questions