Hello,
I have a simple table with 3 fields:
Id - FieldA - FieldB
I need to tracking the history of modifications of FieldA, that can tell me something like:
For Id=101
FieldA was a value of "abc"
between Jan2019 to Mar2019, then value "xyz" between Apr2019 to Jul2019 (Just an example),
and so on.
How can accomplish this?
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE TABLE dbo.YourTable_History
(
Id int NOT NULL
,FieldA <FieldA Type>
,FieldB <FieldB Type>
,SysStartTime datetime2 NOT NULL
,SysEndTime datetime2 NOT NULL
);
GO
CREATE NONCLUSTERED INDEX YourTable_History_NC_SysStartTime_SysEndTime
ON dbo.YourTable_History(SysStartTime, SysEndTime);
GO
CREATE NONCLUSTERED INDEX YourTable_History_NC_Id_SysStartTime_SysEndTime
ON dbo.CitationInfo_History(Id, SysStartTime, SysEndTime);
GO
-- UTC dates stop problems with daylight saving.
ALTER TABLE dbo.YourTable
ADD SysStartTime datetime2 NOT NULL
CONSTRAINT DF_YourTable_SysStartTime DEFAULT SYSUTCDATETIME()
,SysEndTime datetime2 NOT NULL
CONSTRAINT DF_YourTable_SysEndTime DEFAULT '9999-12-31 23:59:59.9999999';
GO
ALTER TABLE dbo.YourTable
ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO
ALTER TABLE dbo.YourTable
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourTable_History, DATA_CONSISTENCY_CHECK = ON));
GO
ALTER TABLE dbo.YourTable ALTER COLUMN SysStartTime ADD HIDDEN;
ALTER TABLE dbo.YourTable ALTER COLUMN SysEndTime ADD HIDDEN;
GO
USE AdventureWorks2012;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('A vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (
2
,3
,261
,1652
,4
,GETDATE()
,GETDATE()
,44594.55
,3567.564
,1114.8638 );
GO
--------------------------------------------------
-- history table
drop table simple_table_history
go
create table simple_table_history
(
old_value varchar(100),
new_value varchar(100),
date_change datetime
)
go
----------------------------------------------
-- Actual table
drop table simple_table
go
create table simple_table
(
Id int ,
FieldA varchar(100),
FieldB varchar(100)
)
go
---------------------------------------------------------
-- insert data main table
insert into simple_table select 1,'abc','john'
insert into simple_table select 2,'def','pam'
go
--------------------------------------------------------
-- trigger on main table
-- only for update
CREATE TRIGGER trigger_simple_table ON simple_table
AFTER UPDATE
AS
BEGIN
insert into simple_table_history ( old_value,new_value,date_change) select Deleted.FieldA,Inserted.FieldA, getdate() from Deleted, Inserted
END
GO
------------------------------------------------------
select * from simple_table
select * from simple_table_history
go
-------------------------------------------------------
-- update Field A
update simple_table set FieldA = 'Harish' where FieldA = 'abc'
-------------------------------------------------------
select * from simple_table
select * from simple_table_history
go