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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With