sql - How To Move an Updated Record into a History Table? -

i have following table:

create table fe_user (     userid int identity (321,4) constraint userid_pk primary key,     username varchar(40) ); 

its corresponding history table is

create table fe_user_hist (     userid int,     username varchar(40),     v_action varchar(50) ); 

every time insert or update occurred on fe_user table, need input newly inserted record or updated record history table.

how can write trigger in t-sql?

here pseducode, alot of errors:

create or replace trigger user_to_hist     after update or delete     on fe_user     each row     declare         v_action varchar(50);     begin         v_action := case when updating 'update' else 'delete' end;         insert fe_user_his(userid, username, v_action)         select :old.userid, :old.username, v_action         .......; end; 

sql server not support create or replace unfortunately. need use either create or alter dependant upon action doing.

also not have row level triggers. affected rows available in pseudo tables called inserted or deleted

the simplest way 2 separate triggers.

for insert

create trigger dbo.tr_i_fe_user     on  dbo.fe_user     after insert  begin     set nocount on; insert fe_user_hist select userid,username, 'inserted' v_action inserted  end 

and update

create trigger dbo.tr_u_fe_user     on  dbo.fe_user     after update  begin     set nocount on; insert fe_user_hist select userid,username, 'updated' v_action inserted /*if wanted previous value instead                  use deleted */  end 

just following on approach mentioned in comments

create trigger dbo.tr_iud_fe_user     on  dbo.fe_user     after insert, update, delete  begin     set nocount on;      insert fe_user_hist     select      isnull(i.userid,d.userid) userid,      case when i.userid null d.username          else i.username     end username,      case when i.userid null 'deleted'           when d.userid null 'inserted'           else 'updated'     end  v_action     inserted full outer join deleted d      on i.userid = d.userid end