Trigger to update table column after insert?

sql, sql-server

Solution

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 TRIGGER` instead of `CREATE TRIGGER` to modify the existing trigger).

BUT: your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table `Inserted` might contain multiple rows.

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

You need to rewrite your entire trigger with the knowledge the `Inserted` WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in `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

Problem

Original source