How to stop insert in a transaction in SQL Server?

Create query using BEGIN TRAN statement to insert two rows within BEGIN TRY if transaction count is greater than 1, catch error and rollback the transaction, otherwise print “transaction committed” (this is my teacher question the idea is that it should not insert more than 1 row by using the @@trancount is greater than one the transaction is rolled back)

This is the code that I wrote

BEGIN TRY
    BEGIN TRAN;

        INSERT INTO [Info].[Country]([name]) VALUES ('Italy');
        PRINT @@TRANCOUNT

        BEGIN TRAN
            INSERT INTO [Info].[Country]([name]) VALUES ('Jorden');
            PRINT @@TRANCOUNT

            IF @@TRANCOUNT > 1
                PRINT 'Rollback the transaction...';
            ELSE 
                PRINT 'transaction succeeded';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH;

It keeps inserting the two rows even though the trans count is greater than 1

How can I fix this?!!

This is the result

Answer

I suppose you’re looking for using @@ROWCOUNT instead of @@TRANCOUNT

BEGIN TRY
  BEGIN TRAN MyTran;
    INSERT INTO Countries(Name) VALUES 
    ('Italy'),
    ('Jorden');
    IF @@ROWCOUNT > 1 
      ROLLBACK TRAN MyTran;
        ELSE
          COMMIT TRAN MyTran;
END TRY
BEGIN CATCH
  RAISERROR ('Error Message',  
             16, -- Severity.  
             1 -- State.  
             ); 
  ROLLBACK TRAN MyTran;
END CATCH;

If you really want to use nested transactions and use @@TRANCOUNT

BEGIN TRY
  BEGIN TRAN MyTran;
    INSERT INTO Countries(Name) VALUES ('Italy');
      BEGIN TRAN MySecTran;
        INSERT INTO Countries(Name) VALUES ('Jorden');
        IF @@TRANCOUNT > 1 
          ROLLBACK TRAN;
            ELSE
              COMMIT TRAN;
END TRY
BEGIN CATCH
    RAISERROR ('Error Message', 
               16, -- Severity.  
               1 -- State.  
               );
    ROLLBACK TRAN;
END CATCH;

Here is a db<>fiddle where you can un-comment one of the two and see how it’s working.

---

Update:

i checked it again. it did not insert any row in the table. but it should insert the first one

Then you need to save the tran as

BEGIN TRY
  BEGIN TRAN MyTran;
    INSERT INTO Countries(Name) VALUES ('Italy');
    SAVE TRAN MySaveTran; 
  BEGIN TRAN MySecTran;
    INSERT INTO Countries(Name) VALUES ('Jorden');
    IF @@TRANCOUNT > 1 
      ROLLBACK TRAN MySaveTran;
  COMMIT TRAN;
END TRY
BEGIN CATCH
  RAISERROR ('Error Message', 
             16, -- Severity.  
             1 -- State.  
             );
  ROLLBACK TRAN;
END CATCH;