SQL 插入后触发更新表列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21596785/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Trigger to update table column after insert?
提问by Mohammed
I need to update a column in table after any record is added in same table
在同一个表中添加任何记录后，我需要更新表中的列
Here is my sql code
这是我的sql代码
CREATE TRIGGER [dbo].[EmployeeInsert]
ON [dbo].[APP_Employees]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EmployeeID AS bigint
SELECT @EmployeeID = ID FROM inserted
UPDATE [dbo].[APP_Employees]
SET [EmployeeTotalNumberOfAnnualLeave] = [EmployeeBalanceTheInitialNumberOfDaysOfAnnualLeaveIn]
WHERE ID=@EmployeeID
END
GO
and showing error
并显示错误
Msg 2714, Level 16, State 2, Procedure EmployeeInsert, Line 17
There is already an object named 'EmployeeInsert' in the database.
消息 2714，级别 16，状态 2，过程 EmployeeInsert，第 17 行
数据库中已经有一个名为“EmployeeInsert”的对象。
回答by marc_s
The error you're getting is because you have that trigger already, in your database. So if you want to create it again, you need to first drop the existing trigger (or use ALTER TRIGGERinstead of CREATE TRIGGERto modify the existing trigger).
您收到的错误是因为您的数据库中已经有该触发器。所以如果你想再次创建它，你需要先删除现有的触发器（或使用ALTER TRIGGER而不是CREATE TRIGGER修改现有的触发器）。
BUT:your fundamental flaw is that you seem to expect the trigger to be fired once per row- this is NOTthe case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Insertedmight contain multiple rows.
但是：您的根本缺陷是您似乎希望每行触发一次触发器-在 SQL Server 中并非如此。相反，触发器每个语句触发一次，并且伪表Inserted可能包含多行。
Given that that table might contain multiple rows - which one do you expect will be selected here??
鉴于该表可能包含多行 - 您希望在这里选择哪一行？
SELECT @EmployeeID = ID FROM inserted
It's undefined - you might get the values from arbitrary rows in Inserted.
它是未定义的 - 您可能会从Inserted.
You need to rewrite your entire trigger with the knowledge the InsertedWILLcontain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted!
您需要使用InsertedWILL包含多行的知识重写整个触发器！您需要使用基于集合的操作 - 不要期望只有一行Inserted！
-- drop the existing trigger
DROP TRIGGER [dbo].[EmployeeInsert]
GO
-- create a new trigger
CREATE TRIGGER [dbo].[EmployeeInsert]
ON [dbo].[APP_Employees]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- update your table, using a set-based approach
-- from the "Inserted" pseudo table which CAN and WILL
-- contain multiple rows!
UPDATE [dbo].[APP_Employees]
SET [EmployeeTotalNumberOfAnnualLeave] = i.[EmployeeBalanceTheInitialNumberOfDaysOfAnnualLeaveIn]
FROM Inserted i
WHERE ID = i.ID
END
GO
