SQL Server 2005: Try Catch with Update on Child Table

Question

I have two tables - tblRequests and tblData. tblRequests has a primary key called recID, and tblData has a foreign key to that primary key, called requestRecID.

On tblRequests, I have a unique index on two columns, which prevents users from entering duplicate rows.

Problem: when I attempt the duplicate insert on tblRequests, it errors out as expected, but my tblData is still updating its foreign key.

So, how do I say "Don’t update tblData if tblRequests insert didn't happen"?

In doing some research, it seems a try/catch would be in order, but I am not at all familiar with this level of SQL.

My code below:

CREATE Procedure [dbo].[spInsert]
(
    @vOpID varchar(3),
    @vSNumb varchar(12)
)
AS
    Declare @vRecID int
BEGIN 
    BEGIN TRANSACTION
        Insert tblRequests
        (
        opID,
        SNumb
        )
        Values 
        (
        @vOpID,
        @SNumb
        )

        Set @vRecID = IDENT_CURRENT ('tblRequests')

    COMMIT TRANSACTION;
    BEGIN TRANSACTION
        Update tblData 
        Set requestRecID = @vRecID 
        Where SNumb = @SNumb And opID = @vOpID 
    COMMIT TRANSACTION;
END

Answer 1

You would need something like this:

• one transaction that spans both operations (because you really want to either have both operations succeed, or then rollback everything - not just part of the transaction...)

• inside a TRY...CATCH block - if the first operation (INSERT) causes error, it'll jump right into the CATCH block and won't execute the second statement, and it will roll back the transaction.

Code:

CREATE Procedure [dbo].[spInsert]
(
    @vOpID varchar(3),
    @vSNumb varchar(12)
)
AS
BEGIN 
    DECLARE @vRecID INT

    BEGIN TRANSACTION
    BEGIN TRY
        INSERT INTO dbo.tblRequests(opID, SNumb)
        VALUES(@vOpID, @SNumb)

        SET @vRecID = SCOPE_IDENTITY()

        UPDATE dbo.tblData 
        SET requestRecID = @vRecID 
        WHERE SNumb = @SNumb AND opID = @vOpID 

        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 TRANSACTION
    END CATCH
END