create table employee(empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))create table employeeHistory(EffectDate [datetime],empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))create trigger UpdateEmployee on employeeafter update ASbegindeclare @empid as [varchar](10)declare @empname as [varchar](50)declare @salary as [decimal](18, 2)declare @status as [varchar](100)select @empid =d.empid from deleted dselect @empname =d.empname from deleted dselect @salary =d.salary from deleted dif update(status)select @status=i.statusfrom deleted ielsebeginselect @status=i.status from deleted iupdate [employee]set status='' where empid=@empidendInsert into employeeHistory([EffectDate],[empid],[empname],[salary],[status])SELECT getdate(), empid, empname, salary,statusFROM deletedendExample:
emp001 xxxx 10000.00 paid
emp002 yyyy 12000.00 paidemp003 zzzz 10000.00 paidemp004 aaaa 15000.00 paidemp005 bbbb 12000.00 paidemp006 cccc 12000.00 paidemp007 pppp 10000.00 paidupdate employee set salary =11000.00 where salary =10000.00
After execute above query the employee returns
emp001 xxxx 10000.00
emp002 yyyy 12000.00 paidemp003 zzzz 10000.00 paidemp004 aaaa 15000.00 paidemp005 bbbb 12000.00 paidemp006 cccc 12000.00 paidemp007 pppp 10000.00 paid
That is after update first updated row set its default value but rest rows don't set it in employee table. Please give me a solution at your earliest.