Query to show when a field has been changed

[Former Member]

Hi

A user of mine needs to know when a user defined field has been updated, he then wants to receive an alert showing the changed record.

The fields I need to include in the query / alert are :T0.[internalSN], T0.[itemCode], T0.[U_LicenceCode], T0.[U_LicenceDate]

The last two are the fields that will trigger the alert.

Any suggestions would be appreciated.

[zal_parchem2]

Hello Neil - the AITM table holds historical information on Items. I am not sure where the User Defined Fields are located in your system, but here is some SQL written a few years back to help one Customer to find out what happened to the Item Master Data Record using AITM. Maybe you can use this as a starting point.

You would want to update the WHERE clause with something like "WHERE DATEDIFF(DAY,T0.UpdateDate, GETDATE()) < 3" which shows all of the updates done in the past three days.

Regards - Zal

COPY WHAT IS UNDER THIS LINE DOWNWARDS TO USE AS SQL

--H-IN Item Info from AITM UoM and Quantity Chgs Ver 1 ZP 2014 06 07

--DESCRIPTION: SQL lists out changes made to Items on the Item Master Data Window. Fields can be changed according to AITM.

--USAGE: Purchasing Agents and Production Personnel

--AUTHOR(s):

--Version 1 Zal Parchem 07 June 2014

SELECT

T0.[ItemCode],

T0.[ItemName],

T0.[BuyUnitMsr],

T0.[NumInBuy],

T0.[UpdateDate]

FROM AITM T0

WHERE

T0.[UpdateDate] > '[%0]'