how to maintain status of records using Date & Timestamp

Question

Hi i have table callled Status where i have the below fields Orderid,Item,Status_new,Status_old,Date,Time,Price,Quantity these are the fields assocaited with this table requirement is to maintain the history of each orderid where there is status changes in the status_new/status_old we need to tracker this item in a seperate table in the given sample data order id 10000901 is have the status on 10/21 value_new status is A and the same order id 10/22 status is changed to status_old to Z this status i need to track in the new table along with other available Quantity fields considering the updated timestamp values in the given date give you some sample data here

Answer 1

Hi Naresh, you could do this using a trigger and a history table. See the script below and test it in a separate database.

use tempdb
go

CREATE TABLE orders	(Orderid int NOT NULL,Item int ,Status_new char(1), Status_old char(1), [Date] date, [Time] time, Price decimal(5,2), Quantity int 
	CONSTRAINT [XPK_orders] PRIMARY KEY(Orderid ASC));  

CREATE TABLE ordershistory	(Historyid int identity not null, Orderid int NOT NULL,Item int ,Status_new char(1), Status_old char(1), [Date] date, [Time] time, Price decimal(5,2), Quantity int
	CONSTRAINT [XPK_ordershistory] PRIMARY KEY(Historyid ASC));  
go

CREATE TRIGGER u_orders on orders
after insert, update
as
	Insert into ordershistory(Orderid,	Item,	Status_new,	Status_old,	[Date],		[Time],		Price,Quantity)
	Select i.Orderid,	i.Item,	i.Status_new,	i.Status_old,	i.[Date],		i.[Time],		i.Price, i.Quantity
	from inserted i
	left join deleted d
		on i.Orderid = d.Orderid
	where i.Status_new <> d.Status_new
	or i.Status_old <> d.Status_old
	or d.Orderid is null
;
go

insert into orders	(Orderid,	Item,	Status_new,	Status_old,	[Date],		[Time],		Price,Quantity)
values				(10000901,	2,		'A',		'','2023-10-21','08:58:00', 100, 10);

Select *
from orders;
Select *
from ordershistory;

update orders 
set Status_new = '', Status_old = 'Z', [Date] = '2023-10-22', [Time] = '08:58:07'
where Orderid = 10000901;

Select *
from orders;
Select *
from ordershistory;
go
DROP TRIGGER u_orders;
DROP TABLE	orders;
DROP TABLE	ordershistory;	

HTH or please describe your intention more detailed.

Answer 2

If you are on SQL 2016 or later, you may want to look at temporal tables. A temporal table is a present-value table and a history table paired to appeared as a single table. SQL Server automatically keeps tracks of changes of the rows.

Answer 3

Hi @Naresh y SQL Server provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.

See this doc: Track data changes (SQL Server).

Also, refer to this article for step-by-step details: SQL Server Change Tracking to Track Columns Updated. Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.