title: SQL Transactions - two inserts
body: <p>I wrote a SQL procedure that inserts data into two tables. I started with <code>begin transaction</code> but some data violate constraint of second table and in the end data are added into first table, but not in second. What is wrong with my code? </p>
  
  <pre><code>create procedure [dbo].[procAddConference]
      @Conf_name varchar(50),
      @Begin_date date,
      @End_date date,
      @Price money,
      @Student_disc numeric(3,2),
      @Limit int,
      @Disc numeric(3,2),
      @Until int 
  as 
      set nocount on
      begin transaction;
      begin try
          IF (@Begin_date &gt; @End_date or @Limit &lt; 0 or @Disc &lt;0 or @Disc &gt;1 or @Student_disc &lt; 0 or @Student_disc &gt; 1)
          BEGIN 
              ; throw 52000, 'Wrong data', 1
          END
  
          insert into Conferences (ConferenceName, BeginDate, EndDate, Price, StudentDiscount)
          values (@Conf_name, @Begin_date, @End_date, @Price, @Student_disc)
  
          IF @@ERROR &lt;&gt; 0
          begin
              RAISERROR('Error, transaction not completed!',16,-1)
              rollback transaction;
          end     
  
          declare @confID INT
          set @confID = @@IDENTITY
  
          declare @duration int
          declare @iterator int
          set @duration = DATEDIFF(dd, @Begin_date, @End_date)
          set @iterator = 0
  
          while @iterator &lt;= @duration
          begin
              insert into ConferenceDay (ConferenceID, Date, Limit) 
              values (@confID, cast(DATEADD(dd, @iterator, @Begin_date) as date), @Limit)
  
              IF @@ERROR &lt;&gt; 0 or @@ROWCOUNT = 0
              begin
                  rollback transaction;
                  RAISERROR('Error, transaction not completed!',16,-1)
              end
  
              set @iterator = @iterator + 1
          end
  
          commit transaction;
      END TRY
      BEGIN CATCH
          DECLARE @errorMsg nvarchar (2048) = 'Cannot add conference . Error message : ' + ERROR_MESSAGE () ;
          ; THROW 52000 , @errorMsg ,1
          rollback transaction;
      END CATCH
  END
  </code></pre>