Prevent deletion on ChgLog table record once transaction is deleted

Epicor ERP 10
1

Hi,
I’ve been looking for a way to retain records in ChgLog Table once specific transaction is deleted in Epicor 10. I didn’t find any means to achieve this, so i decided to try SQL Triggers.

I can retain records if i delete directly in the database but i can’t get same results through Epicor.
Here is my trigger.(I’ve created a custom column to flag deleted records as 1).

CREATE OR ALTER TRIGGER MnzSoftDeleteShipDtlChgLog ON ChgLog
INSTEAD OF DELETE AS
BEGIN
SET NOCOUNT ON;
UPDATE ChgLog
SET IsDeleted_c = 1
WHERE Key1 IN (SELECT KEY1 FROM deleted);
END
GO

Any idea for me to get same results as direct deletion from sql?

2

My Suggestion: Stop doing SQL deletes. That voids your support.

2 Likes
3

I agree with you @Jason_Woods. I did this on testing environment.

I want to achieve same results by using either BPM or any approach in Epicor 10. That’s why am asking here.

4

The only way I know of would be to write to a UD table on delete.

1 Like
5

Thanks @Jason_Woods. I will explore the UD Table option because i want to capture who deleted the record.

In the mean time to avoid touching Epicor tables. I’ve created a trigger to insert deleted record from ChgLog into another custom table.

--######## Custom Table for deleted records in ChgLog ##########
CREATE TABLE [Ice].[MSDMnzChgLog](
	[Company] [nvarchar](8) NOT NULL,
	[Identifier] [nvarchar](32) NOT NULL,
	[SchemaName] [nvarchar](128) NOT NULL,
	[TableName] [nvarchar](128) NOT NULL,
	[Key1] [nvarchar](50) NOT NULL,
	[Key2] [nvarchar](250) NOT NULL,
	[Key3] [nvarchar](50) NOT NULL,
	[DateStampedOn] [date] NOT NULL,
	[LogText] [nvarchar](max) NOT NULL,
	[LogNum] [int] NOT NULL,
	[UserID] [nvarchar](75) NOT NULL,
	[ChgLogMethod] [nvarchar](2) NOT NULL,
	[ChgLogSeq] [int] NOT NULL,
	[SysRevID] [timestamp] NOT NULL,
	[SysRowID] [nvarchar](128) NOT NULL,
	[DateDeleted] [datetime] NOT NULL,
 PRIMARY KEY ([SysRowID])
);

--###### Trigger to Capture deleted records and insert into ice.MSDMnzChgLog #####
CREATE OR ALTER TRIGGER MnzCopyDeleteChgLog ON ice.ChgLog
FOR DELETE
AS
     INSERT INTO ice.MSDMnzChgLog (Company, Identifier, SchemaName, TableName, Key1, Key2, Key3, DateStampedOn, LogText, LogNum, UserID, ChgLogMethod, ChgLogSeq, SysRowID,DateDeleted  )
     SELECT Company, Identifier, SchemaName, TableName, Key1, Key2, Key3, DateStampedOn, LogText, LogNum, UserID, ChgLogMethod, ChgLogSeq, SysRowID,getdate()
     FROM DELETED
GO

Regards,

6

Creating tables in Epicor schemas has been known to blow up upgrades. When you get to 10.2.500+ and SQL Server 2017, you can use SQL CDC to track deletes.

There was also a suggestion at one point to Epicor to start external logging that would capture this activity as well.

7

When I test deleting a record that has Change Log records the Change Log records remain. Are you sure that they are deleted when the ShipDtl record is deleted?

8

That was the behaviour in previous versions unless they have changed in 10.2.600

9

I am testing on 10.2.600.

10

Ooops i didn’t know about this. And am about to upgrade to .600

11

Does it mean that in .600 ChgLog records remain even if specific transaction is deleted?

If that’s the case then i can add a column to flag deleted record and the other column to fetch user who perform the deletion using BPM.

12

I’d be surprised if the records were maintained after a delete, else how do you tell if the ChgLog record info is from this “instance” of the item it’s logging?

For Example, say you have a ChangeLog on OrderDtl.OurQty.

  1. Line 1 is created on the order for a qty of 10.
    This creates the ChgLog record for Key1=OrderNum and Key2 = OrderNum~LineNum, and notes: OurQty 0 -> 10

  2. Line 2 is created on the order for a qty of 5.
    This creates the ChgLog record for Key1=OrderNum and Key2 = OrderNum~LineNum, and notes: OurQty 0 -> 5

  3. Line 2 is deleted from the Order.
    Lets assume the ChgLog record for Key1=OrderNum and Key2 = OrderNum~LineNum remains.

  4. A new line is added, becoming Line 2, and Qty is set to 50.
    The ChgLog record for Key1=OrderNum and Key2 = OrderNum~LineNum already exists, so new updates are added to this existing record. The note OurQty 0 -> 50 is added.

So reviewing the ChgLog for Line 2 of the order, shows the entries from the “first instance” of Line 2 as well.

13

Timestamp?

Separately, if you track booking, it does track that a Order, Line, or Release have been deleted.

1 Like
14

Assuming it timestamped the deletion. Else you’d have to rely on someone’s memory as to when they deleted it. And isn’t this the very reason for the Change Log? :wink:

15

YES!!! That is why ChgLog is NOT really an audit file. Before Epicor Ideas appeared, there was a suggestion to add the capability to track deletes. BTW, not only does it not track deletes, it doesn’t log the initial values of an Add record either - pretty much just the key. And Epicor will tell you that’s why it’s called the CHANGE log! It only tracks changes - not adds nor deletes. :expressionless:

16

For engineering document control we have a NDR(New Document Release) form, for initial releases, and an ECN (Engineering Change Notice) form for changes to existing documents.

I’ve argued that the separate process for the NDR isn’t necessary, as an ECN can do it. People opposed to it say, “But it’s not a change”. My argument is that the change is from “Not existing” to “Now it does”.

2 Likes
17

I just tested it with PartLot records in 10.2.600.4 and the ChgLog records remain. I tested it with a Customer record and it is deleted. I’m not sure if if the ChangeLog delete is BO specific or just weird behavior on our system.

18

@ckrusen Thats why i came up with custom table to track deletion. If you see at the custom table above i have a column DateDeleted which captures exact date and time record was deleted from ChgLog table.

[DateDeleted] [datetime] NOT NULL,

So multiple deletion for Line 2 will be captured with its deletion date. Then i can tell when it was deleted order by DateDeleted.

1 Like
19

It’s the same with me. May be it’s BO specific.

Honestly, I fail to understand why Epicor is not investing on Audit Trail. All these questions here are the result of missing important functionality.

I spoke to them but the answers i get are not satisfactory. :raised_hands:

20

Hmmm … I made the following DD on ChgLog to interrupt the Delete and it worked fine on the OrderDtl table.

image
image621×570 21.7 KB

It doesn’t log the deletion, but does preserve the ChngLog record.

As a somewhat convenient feature, should there be any new additions to the item being logged, it adds the “New Record” line to the ChgLog record, indicating that the prior item had been deleted.

image

1 Like