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

[Kerry Hataley]

Up

0

Down

::

Hi David,

I am thinking that the issue steams from the fact that triggers are NOT executed once per row, but only once per DML operation. So you need to explicit out the triggers. This means that you ALWAYS should have individual triggers per event type. Meaning a trigger of the insert, update and delete. Each need to have their own definition. This hold true when there are other triggers attached to the table. I have seen this issue with Paramount and their triggers.

Also remember to exclude the Dex_xxx from updates, since this will fill your audit table when doing maintenance routines.

[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

[Kerry Hataley]

Up

0

Down

::

Looking at the code quickly, I think the issue is with the Primary Key for RM00101 an the order in which the DMLs are triggering.

Try changing the ‘for UPDATE’ to ‘AFTER UPDATE’.

If not I will dig in more with you…

[David Morinello]

Up

0

Down

::

@kerryhataley

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

If I can’t make this work, I may turn to GPPT for a solution. The trigger solution was the quickest or should have been.

[Kerry Hataley]

Up

0

Down

::

Good Morning @david-morinellotruckpro-com

OK, I can replicate this, give me the weekend to try something…

It all has to do with the fact when calling the insert & deletes from the update trigger temp tables, the insert and delete need to be in a single sql statement using a JOIN.

I can have my solution on Monday for you…

[David Morinello]

Up

0

Down

::

I ended up using GP Power Tools and a variation on the Customer Tracking sample project from the Winthrop GPPT samples site.