title: Transaction has ended in trigger. Batch has been aborted. Derived Attribute
body: <p>I have this trigger : </p>
  
  <pre><code>CREATE trigger [dbo].[DeriveTheAge] on [dbo].[Student]
  after insert,update
  as
  begin
      declare @sid as int;
      declare @sdate as date;
      select @sid= [Student ID] from inserted;
      select @sdate=[Date of Birth] from inserted;
      commit TRANSACTION
      if(@sdate is not null)
      begin
          update Student set Age=DATEDIFF(YEAR,@sdate,GETDATE()) where [Student ID]=@sid;
      end
      print 'Successfully Done'
  end
  </code></pre>
  
  <p>as it suggests, the trigger automatically calculates the Derived attribute "Age" from  the date of birth. But I get this error when I do the insert : </p>
  
  <pre><code>(1 row(s) affected)
  Successfully Done
  Msg 3609, Level 16, State 1, Line 1
  The transaction ended in the trigger. The batch has been aborted.
  </code></pre>
  
  <p>Initially I avoided this error because the rows were getting updated inspite of the error. But now when I am inserting a record from the FORNT END, the record is not updated. Instead, it throws this exception : <img src="https://i.stack.imgur.com/Nqldc.jpg" alt="enter image description here"></p>
  
  <p>Can anyone please help me out?</p>
  
  <p>btw, mine is SQL Server 2008 R2 and Visual Studio 2010.</p>
  
  <p><strong>CORRECTION</strong> : The Records are still getting updated. But the Exception is the Vilan.</p>
  
  <p><strong>Update</strong></p>
  
  <pre><code>CREATE TRIGGER [dbo].[DeriveTheAge] 
  ON [dbo].[Student]
  FOR INSERT, UPDATE
  AS
  BEGIN
      UPDATE s 
        SET Age = DATEDIFF(YEAR, [Date of Birth], CURRENT_TIMESTAMP)
        FROM dbo.Student AS s
        INNER JOIN inserted AS i
        ON s.[Student ID] = i.[Student ID]
        WHERE i.[Date of Birth] IS NOT NULL;
        commit transaction
  END
  GO
  </code></pre>