sql - 插入后触发更新表列？

Question

在同一表中添加任何记录后，我需要更新表中的列

这是我的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

并显示错误

Msg 2714, Level 16, State 2, Procedure EmployeeInsert, Line 17
There is already an object named 'EmployeeInsert' in the database.

Answer 1

您收到的错误是因为您的数据库中已经有该触发器。因此，如果您想再次创建，则需要先删除现有触发器(或使用ALTER TRIGGER而不是CREATE TRIGGER来修改现有触发器)。

但是:你的根本缺陷是你似乎期望触发器每行触发一次 - 这不是不是的情况SQL 服务器。相反，触发器每个语句触发一次，并且伪表Inserted可能包含多行。

鉴于该表可能包含多行 - 您希望此处选择哪一行？

SELECT @EmployeeID = ID FROM inserted 

它是未定义的 - 您可能会从 Inserted 中的任意行获取值。

您需要重写整个触发器，并了解插入将包含多行!您需要使用基于集合的操作 - 不要期望 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 [dbo].[APP_Employees].ID = i.ID
END
GO