How to avoid Failure Transaction ?..

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