交易已在触发器中结束。批次已中止。派生属性 [英] Transaction has ended in trigger. Batch has been aborted. Derived Attribute

问题描述

我有此触发器：

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.

最初，我避免了此错误，因为尽管有错误，但行仍在更新。但是现在当我从FORNT END插入记录时，该记录不会更新。相反，它将引发此异常：

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 :

有人可以帮帮我吗？

btw，我的是SQL Server 2008 R2和Visual Studio 2010。

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

更正：记录仍在更新中。但是，例外是Vilan。

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

更新

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

推荐答案

为什么要提交触发器？为什么不处理多行插入或更新？您不能只是声明变量并从插入状态开始分配变量-您认为在更新2或15或6000行时会分配什么值？

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.

此外， DATEDIFF（YEAR ）并不是一种可靠的计算年龄的方法，它所做的只是计算已经越过的年份边界的数量，不知道该人的实际生日是1月1日或12月31日，还是介于两者之间的任何时间。

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?

这篇关于交易已在触发器中结束。批次已中止。派生属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！