Transaction still commits after violation of unique constraint

Question

I have a stored procedure that is in this format (simplified):

SET XACT_ABORT ON
GO
BEGIN TRY
BEGIN TRANSACTION myTransaction
--multiple CRUD operations
--insert statement inserts records into table with unique constraint.

    IF (XACT_STATE()) = 1
      BEGIN     
        COMMIT TRANSACTION myTransaction
        RAISERROR ('TRANSACTION COMMITTED', 0, 1) WITH NOWAIT
        RETURN 1
      END
END TRY

BEGIN CATCH
    IF (XACT_STATE()) = -1
      BEGIN
        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 myTransaction
        RAISERROR ('TRANSACTION ROLLED BACK', 0, 1) WITH NOWAIT
        RETURN 0
      END
    IF (XACT_STATE()) = 1
      BEGIN         
        COMMIT TRANSACTION myTransaction
        RAISERROR ('TRANSACTION COMMITTED', 0, 1) WITH NOWAIT
        RETURN 1
      END
END CATCH

I'm trying to address an issue where the SP tries to insert records into a table with a unique constraint, and it fails, so it doesn't insert anything. The problem is, this doesn't seem to trigger the catch block, or change the XACT_STATE() to reflect that there's a problem. What am I doing wrong?

The desired functionality would be that if the constraint is violated, the entire transaction would fail and be rolled back.

Answer 1

As you're using

Set Xact_Abort On

My understanding is that the transaction has ALREADY been rolled back by the time you get to any Catch part. There is nothing to rollback.

There's also something I seem to remember about there being times when such an instance would bypass the Catch altogether.