January 25, 2008 at 5:05 am
I have a database about bank information which stores critical information of account holders.The data has grown too large and
the client has asked me to delete the data of some customers who
have closed the account long time. There are about two hundered
tables in the database. The data to be deleted is from around 150
tables and most of them have foreign key constraint. I know the exact table relations which links to which table. I have designed the procedure to delete the data in around 150 tables, before I was using @@error to rollback if there is any error so that the data should be deleted in all the 150 tables or none of them. In 2005 I came across TRY CATCH which is new to me. I have re-designed the
procedure using TRY CATCH block as shown below.
CREATE PROC TEST @Customerid int
as
begin
BEGIN TRY
DELETE FROM TABLE1 WHERE customerid = @customerid
DELETE FROM TABLE2 WHERE customerid = @customerid
DELETE FROM TABLE3 WHERE customerid = @customerid
.
.
.
.
.
DELETE FROM TABLE150 WHERE customerid =
@customerid
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT,
@ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,
@ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
COMMIT TRANSACTION
end
It works fine if there is no error, otherwise if there is a foreign key constraint error , the table upto which the we do not get error the data is deleted rest of the tables after the error have the data intact. Have I gone wrong in Rollback tran or is the logic wrong?
January 25, 2008 at 5:22 am
You must declare the transaction within the try
BEGIN TRY
Begin Transaction
DELETE FROM #TABLE1 WHERE customerid = @customerid
DELETE FROM #TABLE2 WHERE customerid = @customerid
DELETE FROM #TABLE3 WHERE customerid = @customerid
Commit transaction
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT, @ErrorNumber INT,
@ErrorMessage NVARCHAR(4000), @ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity,
@ErrorState, @ErrorNumber)
IF XACT_STATE() < 0
ROLLBACK TRANSACTION
END CATCH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply