Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[David Morinello]

Up

0

Down

::

I have tried “After Update” and “For Update”.

The inserts into the tracking table are not attempting to insert the Dex_Row_ID. I have attached the SQL for the audit table and for the matching trigger. What am I missing?

tr_RM00101_ChangeTracking-v1_0.sql_.txt 3 KB Text File - Click to view

• Copy Download Link

USE TRUCK GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================================================================= -- Author: David Morinello -- Create date: 8/16/2023 -- V 0.5 - Tracks Customer Name, Customer Name, Credit Limit Amount, Payment Terms ID, & Primary BilltoAddress Code -- to the Table RM00101_Audit_Tracking -- Description: Monitor for RM00101 changes to the RM00101_Audit_Tracking Change Audit Table -- Proof of Concept Testing with One Field -- ============================================================================================= --New SQL 2016 command CREATE OR ALTER TRIGGER [dbo].[tr_RM00101_ChangeTracking] ON RM00101 FOR UPDATE AS --BEGIN SET NOCOUNT ON; IF (UPDATE (CUSTNAME) OR UPDATE (CRLMTAMT) OR UPDATE (PYMTRMID) ) --IF (UPDATE(CUSTNAME)) BEGIN -- Audit OLD record INSERT INTO RM00101_Audit_Tracking SELECT del.CUSTNMBR --CUSTNMBR ,del.CUSTNAME --CUSTNAME ,del.CRLMTAMT --CRLMTAMT ,del.PYMTRMID --PYMTRMID ,del.PRBTADCD --PRBTADCD ,'Original' --ChangeNote ,GETDATE() --ChangeDate ,ORIGINAL_LOGIN() --ChgUser FROM DELETED AS del --FROM DELETED AS del INNER JOIN INSERTED AS ins --ON del.CUSTNMBR = ins.CUSTNMBR AND del.ADRSCODE = ins.ADRSCODE END BEGIN -- Audit NEW record INSERT INTO RM00101_Audit_Tracking SELECT ins.CUSTNMBR ,ins.CUSTNAME ,ins.CRLMTAMT ,ins.PYMTRMID ,ins.PRBTADCD ,'Changed' ,GETDATE() ,ORIGINAL_LOGIN() FROM INSERTED AS ins --FROM DELETED AS del INNER JOIN INSERTED AS ins -- ON del.CUSTNMBR = ins.CUSTNMBR --AND del.ADRSCODE = ins.ADRSCODE END --END GO ALTER TABLE [dbo].[RM00101] ENABLE TRIGGER [tr_RM00101_ChangeTracking] GO /* exec sp_settriggerorder @triggername = 'tr_RM00101_ChangeTracking', @order = 'last', @stmttype = 'UPDATE', @namespace = null GO SELECT name, IsFirst = OBJECTPROPERTY(object_id, 'ExecIsFirstInsertTrigger'), IsLast = OBJECTPROPERTY(object_id ,'ExecIsLastInsertTrigger') FROM sys.triggers WHERE parent_id = OBJECT_ID(N'dbo.RM00101'); */ --ALTER TABLE [dbo].[RM00101] DISABLE TRIGGER [tr_RM00101_ChangeTracking] --DROP Create TRIGGER tr_RM00101_ChangeTracking /* USE [TRUCK] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RM00101_Audit_Tracking]( [CUSTNMBR] [CHAR](15) NOT NULL, [CUSTNAME] [CHAR](65) NULL, [CRLMTAMT] NUMERIC (19,5) NULL, [PYMTRMID] [CHAR](21) NULL, [PRBTADCD] [CHAR](15) NULL, [ChangeNote][CHAR](15) NOT NULL, [ChangeDate] [DATETIME] NULL, [ChgUser] [NVARCHAR](128) NOT NULL, [DEX_ROW_TS] [DATETIME] NOT NULL, [DEX_ROW_ID] [INT] IDENTITY(1,1) NOT NULL, CONSTRAINT [PKRM00101_Change] PRIMARY KEY NONCLUSTERED ( [CUSTNMBR] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [P[PRIMARY] GO */

Expand

Create-RM00101_Audit_Tracking-Table.sql_.txt 2 KB Text File - Click to view

• Copy Download Link

/******************************************************************************** Target Server : GPPDBAWS01 Target Database: TRUCK Author : David Morinello Date Created : 08/16/2023 Version : 0.5 Table Design Inwork Purpose : Customer Audit-Table and Trigger to Track Customer Master table changes Note : Create RM00101_Audit_Tracking Table.sql : https://truckprovsts.visualstudio.com/TruckPro%20Portfolio%20Project/_workitems/edit/25742 ********************************************************************************/ USE [TRUCK] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DROP TABLE IF EXISTS [dbo].[RM00101_Audit_Tracking] --SQL 2016 Feature CREATE TABLE [dbo].[RM00101_Audit_Tracking]( [CUSTNMBR] [CHAR](15) NOT NULL, [CUSTNAME] [CHAR](65) NULL, [CRLMTAMT] NUMERIC (19,5) NULL, [PYMTRMID] [CHAR](21) NULL, [PRBTADCD] [CHAR](15) NULL, [ChangeNote][CHAR](15) NOT NULL, [ChangeDate] [DATETIME] NULL, [ChgUser] [NVARCHAR](128) NOT NULL, -- [DEX_ROW_TS] [DATETIME] NOT NULL, [DEX_ROW_ID] [INT] IDENTITY(1,1) NOT NULL, CONSTRAINT [PKRM00101_Change] PRIMARY KEY NONCLUSTERED ( [CUSTNMBR] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] GO --ALTER TABLE [dbo].[RM00101_Audit_Tracking] ADD CONSTRAINT [DF__RM00101_Audit_Tracking__DEX_ROW__1154E6D5] DEFAULT (GETUTCDATE()) FOR [DEX_ROW_TS] --GO /* Select * from RM00101_Audit_Tracking sp_helptrigger RM00101 */

Expand