Dynamics - How to log the user that changed a field in the database

I have a solution that will 'audit' changes to the database... but it doesn't log who did it. Also, my existing solution is too heavy handed... it logs every change to every field. This short script will show you how to log the changes to just one field and who did it.

In our example we'll log the changes to the Customer PO Number field in the SOP Work Header table.

Related Articles

... and you 'll find more on the SQL (General) Menu

Creates the table that the trigger logs to. Change ‘_TEMP’ before you run this.

select top 1 sopnumbe, soptype, cstponbr, GETDATE() as Created, 0 as  DEX_ROW_ID, SPACE(15) as USERID
      INTO _TEMP
      FROM SOP10100

The trigger. Again, rename ‘_TEMP’

IF OBJECT_ID ('dbo.t_SOP10100_UPD','TR') IS NOT NULL
   DROP TRIGGER dbo.t_SOP10100_UPD 
GO
  
CREATE TRIGGER dbo.t_SOP10100_UPD 
   ON  dbo.SOP10100
   AFTER UPDATE
AS 
  
--declare variables
DECLARE @sopnumbe varchar(21)
declare @cstponmb varchar(21)
DECLARE @SOPTYPE SMALLINT     
  
  
--insert into our custom table our 'audit' values
--use the DELETED table to be sure that we're only logging changed rows, 
--not every time the table is updated
INSERT INTO _TEMP (SOPNUMBE, SOPTYPE, CSTPONBR, Created  , DEX_ROW_ID, USERID)
    SELECT         SOPNUMBE, SOPTYPE, CSTPONBR, GETDATE(), DEX_ROW_ID, SYSTEM_USER
        FROM INSERTED ins
            join DELETED del on del.dex_row_id = ins.dex_row_id
        WHERE SOPNUMBE = @sopnumbe 
            AND SOPTYPE = @SOPTYPE
            and ins.cstponbr <> del.cstponbr
                 
  
  
GO

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences