How to avoid Failure Transaction ?..

Question

Dear Friends I have done one page with flaws. in that particular page i am added the items in grid  and after that i am saving the whole record in POHEADER Table The items which i added in Grid which is stored in PODETAILS Table Seperately. Both Table related using the Primary key .The Things is when the user press any wrong key or any interruption with that page.The Particular Record partially saved which means POHEADER Table no record PODETAILS have the Record with POHID Column is null in the PODETAILS Table.. How should i avoid this Issue . IF it is Possible to anybody ! Help me out .. Thanks in advance

Iftikar Hussain · Answer

Hi Since while saving the data the details need to be saved in both the table. Therefore I suggest create once stored procedure and save details in both the table by using TRANSACTION. BEGIN TRY BEGIN TRAN IF NOT EXISTS(SELECT POHDR_PKey FROM POHEADER WITH(NOLOCK) WHERE POHDR_PKey=@POHDR_PKey) BEGIN //Insert into POHEADER END ELSE BEGIN //UPDATE POHEADER for @POHDR_PKey END DELETE FROM PODETAILS WHERE POHDR_FKey=@POHDR_PKey //INSERT INTO PODETAILS COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE() @ErrorSeverity = ERROR_SEVERITY() @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage@ErrorSeverity@ErrorState); END CATCH; Regards Iftikar