SQLTeam.com | Weblogs | Forums

Trigger for tracking time modifications

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?

Thanks a lot in advance.

Luis

With SQL2016 and above I would use versioning;

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
1 Like

Sorry, I have SQL SERVER 2014, I've not mentioned before.

Luis

hi another option

is to

use a trigger on the table ....
which automatically captures the column changes you want

into another table

:slight_smile: hope this helps

1 Like

How?

please see this example

please click arrow to the left for trigger SAMPLE
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

Could you help me write it?

Luigi

I will write it for you !!!! please see if it works for you !!!!!

Hi Heres the code

please click arrow to the left for SCRIPT
--------------------------------------------------
-- 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

1 Like

Perfect, thank you so much Harish.
Luis

This post was flagged by the community and is temporarily hidden.

Hi Ifor

Thanks for pointing that out ...

I did not pay that much attention to the trigger ...
I just did something very quickly ...

Best practices etc are another subject ...

That involves either
experience
or ' part of thinking checklist '

Thanks :slight_smile: :slight_smile: