TREAT

DECLARE @referenceId INT;

SET @referenceId = <>;

DECLARE @onlineFormDefId INT;

SET @onlineFormDefId = <>;

DECLARE @onlineFormId INT;

DECLARE @STATUS_PENDING INT;

SET @STATUS_PENDING = 1;

BEGIN TRANSACTION deleteOF;

BEGIN TRY

    -- Get the online form that will be deleted

    SET @onlineFormId = (SELECT OnlineFormID FROM TR_OnlineForm WHERE OnlineFormDefID = @onlineFormDefId AND  ReferenceID = @referenceId AND Status = @STATUS_PENDING);

    IF @onlineFormId IS NULL OR @onlineFormId <= 0

    BEGIN

        RAISERROR('Could not find the online form for ReferenceID %d and status Pending.', 16, 1, @referenceId);

    END;

    DELETE FROM TR_OnlineFormAnswerVersion WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineFormAnswers WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineFormAttachmentContent WHERE OnlineFormAttachmentID IN (SELECT OnlineFormAttachmentID FROM TR_OnlineFormAttachment WHERE OnlineFormID = @onlineFormId);

    DELETE FROM TR_OnlineFormAttachment WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineFormClientPastInfo WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineFormClient WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineFormEmail WHERE OnlineFormID = @onlineFormId;

    DELETE FROM TR_OnlineForm WHERE OnlineFormID = @onlineFormId;

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;

    IF @@TRANCOUNT > 0

    BEGIN

        ROLLBACK TRANSACTION deleteOF;

    END;

END CATCH;

IF @@TRANCOUNT > 0

BEGIN

    COMMIT TRANSACTION deleteOF;

END;

GO