Transaction has ended in trigger. Batch has been aborted. Derived Attribute

Question

I have this trigger :

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

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 :

(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.

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 :

Can anyone please help me out?

btw, mine is SQL Server 2008 R2 and Visual Studio 2010.

CORRECTION : The Records are still getting updated. But the Exception is the Vilan.

Update

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

Faint

Answer 1

Why are you committing in the trigger? Why are you not handling multi-row inserts or updates? You can't just declare variables and assign them from inserted - what values do you think will get assigned when you update 2, or 15, or 6000 rows?

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;
END
GO

That all said, why on earth would you need a trigger to calculate someone's age? You can get this from the birth date right now at query time and know that it will be accurate, unlike this stale value you've stored in the table. Note that if their row is not updated for over a year, the age you've put in the table is out of date. When do you go back and update the Age for all rows in the table? Once a day? Anything less and your Age column is completely unreliable and pointless.

Also, DATEDIFF(YEAR is not a reliable way to calculate age in the first place. All it does is count the number of year boundaries that have been crossed, it has no idea if the person's actual birthday is Jan 1 or Dec 31 or anywhere in between.

Finally, I wouldn't print from the trigger. Who is going to consume that print statement when you're not debugging?

Orcein

Thank you very much for the response... Actually my front end insertes only one record at a time, hence the code. And yes I now agree with your suggestions about the Age column. It is really a bad idea. Also I get that the print statement is not required. Actually it was written for checking purposes and then left unremoved. Finally, can you suggest a good method for getting the Age of the Student? – Faint

Probably as a function that is only called when you actually need the age. #58099 – Orcein

I wish I could give you more than just a one up-vote! Thank you again. – Faint

Just curious; Why actually did this abortion of batch took place? I know the basic reason is that of commiting inside the trigger. But if I had not done so, What would have happened with the batch so as not to result in this abortion? – Faint

The batch was aborted because you committed in the trigger, and then presumably tried to commit again - is it possible that your calling code that performed the insert did so using transactions (either BEGIN TRANSACTION explicitly or using Transaction properties on the connection)? – Orcein

There is no explicit commiting of transaction from my side. And the same error message comes when I try to insert using the ad-hoc "insert into table values" query from the Query editor! – Faint

Even after you removed the commit from the trigger? If you want anyone to be able to figure this out, please post the updated trigger code, the table structure, and the insert statement that causes the error. – Orcein

No the commit transaction is still there in the trigger. Ill post the trigger in few seconds. – Faint

I think since any transaction is by default commited, hence the error. Am I right? – Faint

Remove the commit from the trigger. Or stop doing this in a trigger in the first place - create a view. Then you calculate it when you need it and it is guaranteed to be accurate at that time. – Orcein