Create Both tables.
Create table Emp (
EmpId varchar(5),
EmpName varchar(40)
)
Create table EmployeeDetailsHist (
EmpId varchar(20)
, EmpName varchar(40)
, HistoryDate datetime
)
After update Trigger
create TRIGGER EmpAfterUpdate
ON Emp
AFTER UPDATE
AS
IF ( UPDATE (EmpId) OR UPDATE (EmpName) )
BEGIN
INSERT INTO EmployeeDetailsHist (EmpId, EmpName, HistoryDate)
select EmpID, EmpName, getdate()
from deleted ;
END;
Existing data
1 Kamal
2 surya
Update Emp set EmpName = 'Raj' where EmpId = '1'
select
'Current' as Origin, EmpId, EmpName from Emp
union all
select 'History' as Origin, EmpId, EmpName from EmployeeDetailsHist
Result is:
Status EmpId Name
------------------------
Current 1 Raj
Current 2 Surya
History 1 Kamal