Is the a way to show when customer name was changed from Name A to Name B apart from using ChgLog table?
You could write a BPM to log change history data to some UD field(s)/table(s)… But what’s wrong with using the built-in change log?
Thanks for the advice. Built-in change log is very huge for us.
one thing that change logs don’t record is when the record is deleted.
While not a log, you could fire off an email when it changes. Just include the custNum, from/to (for both the ID and description) and the userid
And Add only includes the key but no field details - unless that has changed recently.
Subsequent changes would show the old value. So you could infer the value when the record was created
And I should have been more complete … Deleting the record the change log is tied to, deletes the change log as well.
Sure. I’ve had to accept that the ChgLog is a Change Log and not an audit log. And a resilient audit log does not store the log in the same datastore as the data as a best practice.
I wanted to retain deleted records from ChgLog Table. So, I decided to create a custom table and Trigger to capture any deleted record from ChgLog table. Unfortunately, am not able to capture the user performing the action.
- Custom Table
CREATE TABLE [Ice].[MnzavaChgLogTab]( [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] [uniqueidentifier] ROWGUIDCOL NOT NULL, [DateDeleted] [datetime] NOT NULL, PRIMARY KEY CLUSTERED ( [SysRowID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
- Delete Trigger
CREATE OR ALTER TRIGGER MnzavaChgLog ON ice.ChgLog FOR DELETE AS INSERT INTO ice.MnzavaChgLogTab (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
UD tables. Use them.
Regarding “Deleted record tracking”… as many above have confirmed, change logs get deleted when you delete the record. BUT This is a moot point for some records… for example, it is not possible to delete a part number of a customer if there is any activity against that item… so yes, in theory, if you create a customer, change it several times, and then delete it, the change log is deleted as well…
BUT… try to delete the record after you have created a quote or order… you will find it is not allowed… and personally… “who cares” if someone creates a customer and later deletes it after as long as it is not used. “who cares” if someone creates a part number, and it never gets used, so they delete it?
BUT… there are some records that it DOES matter… you can create a SALES ORDER, do a bunch of changes, and then subsequently delete that order. MRP may have planned for purchases… POs may have been created, and then someone deleted the order. There is no tracking that it was there. This is where you CAN create a BPM that tracks deleted Orders. The BPM can capture the deletion and put an entry into a UD Table showing what was deleted.
OR the BETTER option is to make a BPM that DISALLOWS orders from being deleted. Instead force the sales admin to simply mark the order as CANCELLED (this is my preference… i never like to delete history).
Also, in the case of Sales Orders, the Booking tables will keep track of additions, changes, and deletions.
I think the more practical example is an OrderDtl. Say you only log changes (not the initial creation of the record). Then an order lines was created then deleted, and then re-created. There would be no change log for the particular OrderDtl.OrderLine. So it would appear to be have never been changed.
Yes, I agree, and that falls into my rule that “history should not change”… you can make a BPM to prohibit deletes and force them to CANCEL the line.
Tim - I made the rather large assumption that the ChgLog records were deleted when the item the relate to is deleted. This was based on the fact the you can’t pull up the change log related to a record if that record doesn’t exist. But I never actually checked to see if the change log records were deleted. Can you confirm that the change log records are actually deleted?
This needs to be asked more often in ERP implementations…
I’m asked that WAY too often, by people that don’t understand how things work under the hood.
Lol, fair enough. I meant the flipside… users insist we NEED to do abc because of xyz, and we don’t stand up often enough and say “who cares about xyz”?
I’m right there with you brother.
Good idea. I will use UD Table instead.
Your assumption is correct. if you have a change log on the Part table, that tracks add/modify of the part description:
- you will get one log entry when the part is initially created (this is unnecessary because nothing changed, and there is a creation date on the part.)
- When you change the description, a new change log entry is created with the old and new descriptions.
- When you delete the part number (because it was mistakenly entered)… as long as it is not used (in a BOM, Sales Order, Job, PO, Inventory, etc), it will allow it to be deleted.
- As part of the deletion process, it will also delete all the Change Log Entries for the part.