The transaction ended with a trigger. The package was interrupted. 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 he suggests, the trigger automatically calculates the Derived attribute "Age" from the date of birth. But I get this error when I 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. 

I initially avoided this error because the rows were updated in addition to the error. But now, when I insert a record from FORNT END, the record is not updated. Instead, it throws this exception:

Can anyone help me out?

btw, my SQL Server 2008 R2 and Visual Studio 2010.

CORRECTION . Records are still being updated. But the Exception is 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 

Answer 1

Why do you trigger? Why don't you handle multi-line inserts or updates? You cannot just declare variables and assign them from the inserted ones - what values ​​do you think will be assigned when updating 2 or 15 or 6,000 lines?

 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 

What everyone said, why do you need a trigger to calculate someone's age? You can get this from the date of birth right now at the time of the request and know that it will be accurate, unlike this obsolete value that you saved in the table. Please note that if their row is not updated for more than a year, the age that you put in the table is out of date. When will you come back and update Age for all rows in the table? Once a day? Anything smaller and the “Your Age” column are completely unreliable and pointless.

Also, DATEDIFF(YEAR not a reliable way of calculating age in the first place. Everything he does is count the number of year borders that have been crossed, he does not know if the person’s actual birthday is January 1 or December 31 or where- then in between.

Finally, I will not print from the trigger. Who is going to use this print statement when you are not debugging?