CODEWITHSUNDEEP
sqlsql-server
Mohammed
Mohammed

Reputation: 203

Trigger to update table column after insert?

I need to update a column in table after any record is added in same table

Here is my sql code

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.

Upvotes: 7

Views: 63079

Answers (1)

marc_s
marc_s

Reputation: 755381

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

Upvotes: 33

Related Questions