Store historical records in a history table in SQL Server

You can maintain historical changes made in a table by creating another table with the same schema which holds historical data.

SomeTable is the base table containing current data rows

SomeTable-History has exact same schema with 2 additional columns
trans_type varchar(10)
date_time datetime
and this table will hold historical data

Below triggers created on SomeTable for DELETE, INSERT and UPDATE to reflect changes in SomeTable-History

create trigger SomeTable_LogDelete on dbo.SomeTable for delete
as
	declare @Now as DateTime = GetDate()
	set nocount on
	insert into SomeTable-History
	select *, 'deleted', @Now
	from deleted
go

exec sp_settriggerorder @triggername = 'SomeTable_LogDelete', @order = 'last', @stmttype = 'delete'
go

create trigger SomeTable_LogInsert on dbo.SomeTable for insert
as
	declare @Now as DateTime = GetDate()
	set nocount on
	insert into SomeTable-History
	select *, 'inserted', @Now
	from inserted
go

exec sp_settriggerorder @triggername = 'SomeTable_LogInsert', @order = 'last', @stmttype = 'insert'
go

create trigger SomeTable_LogUpdate on dbo.SomeTable for update
as
	declare @Now as DateTime = GetDate()
	set nocount on
	insert into SomeTable-History
	select *, 'upd-deleted', @Now
	from deleted
	insert into SomeTable-History
	select *, 'upd-inserted', @Now
	from inserted
go

exec sp_settriggerorder @triggername = 'SomeTable_LogUpdate', @order = 'last', @stmttype = 'update'
go