In the query we write to several tables. If data was written to the first, and an exception was thrown when writing to the second, will the record to the first be rolled back?

Question

Database

In this case, the following structure is used in the request:

DECLARE @first SMALLINT 
    SET @first = 0

    IF (NOT EXISTS(SELECT ........))
    BEGIN
        IF(EXISTS(SELECT ........))
            SET @first = 0
        ELSE
            SET @first = 1

        BEGIN TRY
            INSERT INTO {0}
            ([ID])
            OUTPUT  INSERTED.ID)
            VALUES (@Id)

            INSERT INTO {2} (ID], [CONTENT])
            VALUES (@Id, @Content)

            IF @first = 1
            BEGIN
            ALTER TABLE {0}
              WITH CHECK ADD CONSTRAINT CK_{0}_{3}_{4}
                CHECK( ... ) 
                OR ( ... ))

                UPDATE TABLES SET BEGIN_INDEX_{3} = {1} WHERE NAME_TABLE = '{0}'
            END
        END TRY
        BEGIN CATCH
            declare @ErrorNumber int
            select @ErrorNumber = ERROR_NUMBER()
            IF @ErrorNumber not in (2601, 2627)
            BEGIN
                declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
                select @ErrorMessage = cast(@ErrorNumber as nvarchar) + ' ' + ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), 
                        @ErrorSeverity = ERROR_SEVERITY(), 
                        @ErrorState = ERROR_STATE();
                RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
            END
        END CATCH
    END

Answer 1

if you open a transaction, then of course
, that is, open a transaction, all updates to a triblock, and if an error is made, rollback, if everything is fine, then commit