title: Trigger to block insert into SQL Server
body: <p>I have a requirement to prevent insert into table using after insert based on certain condition. When I am calling insert statement directly, it is executing perfectly without any problem. Whereas when am using procedure for insert statement with transaction scope, I am getting this error</p>
  
  <blockquote>
    <p>The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>My code:</p>
  
  <pre><code>create table test
  (
      id int ,
      name varchar(10)
  )
  
  create table test1
  (
      id int ,
      name varchar(10)
  )
  
  ALTER PROCEDURE test_insert 
      @id INT, @name NVARCHAR(10)
  AS
  BEGIN
      BEGIN TRY
          BEGIN TRANSACTION
              INSERT INTO test1 (id, name) 
              VALUES (@id, @name)
  
              INSERT INTO test (id, name) 
              VALUES (@id, @name)
         COMMIT
      END TRY
      BEGIN CATCH
          ROLLBACK;
  
          DECLARE @errormsg NVARCHAR(MAX)
  
          SELECT @errormsg = ERROR_MESSAGE();
          THROW 500001, @errormsg, 1;  
      END CATCH
  end
  
  ALTER TRIGGER TRG_test
  ON dbo.test
  AFTER INSERT AS
  BEGIN
      DECLARE @idNum INT
  
      SELECT @idNum = id FROM inserted
  
      IF @idNum = 1
      BEGIN
          RAISERROR('error', 1,1);
          ROLLBACK TRANSACTION
          RETURN  
      END
  END
  </code></pre>
  
  <p>Please let me know if am missing anything</p>