sql - Trigger to update table column after insert? -

i need update column in table after record added in same table

here sql code

create trigger [dbo].[employeeinsert] on  [dbo].[app_employees] after insert  begin    set nocount on;  declare @employeeid bigint  select @employeeid = id inserted   update [dbo].[app_employees]  set  [employeetotalnumberofannualleave] = [employeebalancetheinitialnumberofdaysofannualleavein]  id=@employeeid   end go 

and showing error

msg 2714, level 16, state 2, procedure employeeinsert, line 17
there object named 'employeeinsert' in database.

the error you're getting because have trigger already, in database. if want create again, need first drop existing trigger (or use alter trigger instead of create trigger modify existing trigger).

but: fundamental flaw seem expect trigger fired once per row - not case in sql server. instead, trigger fires once per statement, , pseudo table inserted might contain multiple rows.

given that table might contain multiple rows - 1 expect selected here??

select @employeeid = id inserted  

it's undefined - might values arbitrary rows in inserted.

you need rewrite entire trigger knowledge inserted will contain multiple rows! need work set-based operations - don't expect single row in inserted !

-- drop existing trigger drop trigger [dbo].[employeeinsert]  go  -- create new trigger create trigger [dbo].[employeeinsert] on [dbo].[app_employees] after insert  begin        set nocount on;      -- update table, using set-based approach     -- "inserted" pseudo table can ,     -- contain multiple rows!     update [dbo].[app_employees]      set  [employeetotalnumberofannualleave] = i.[employeebalancetheinitialnumberofdaysofannualleavein]      inserted     id = i.id end go