In a previous post I mentioned that we had a need to track changes to the Dynamics GP vendor master data, which is stored in the database as company table PM00200. There are 3 operations we need to track at the database level: inserts, deletes, and updates. I showed how we track inserts and deletes.

They've left a trail, Watson!

They’ve left a trail, Watson!

Updates are a little more complicated, but they are not that bad.

First, I want to remember more than just the vendor ID. Plus, I want to know what the old values were, along with the updated values. So the table to store the logging information has a few more columns in it.

[sql]
CREATE TABLE [dbo].[SSC_LOG_PM00200_UPDATE](
[VENDORID] [char](15) NOT NULL,
[OLD_VENDNAME] [char](65) NOT NULL,
[NEW_VENDNAME] [char](65) NOT NULL,
[OLD_VNDCHKNM] [char](65) NOT NULL,
[NEW_VNDCHKNM] [char](65) NOT NULL,
[OLD_VNDCLSID] [char](11) NOT NULL,
[NEW_VNDCLSID] [char](11) NOT NULL,
[OLD_ADDRESS1] [char](61) NOT NULL,
[NEW_ADDRESS1] [char](61) NOT NULL,
[OLD_ADDRESS2] [char](61) NOT NULL,
[NEW_ADDRESS2] [char](61) NOT NULL,
[OLD_ADDRESS3] [char](61) NOT NULL,
[NEW_ADDRESS3] [char](61) NOT NULL,
[OLD_CITY] [char](35) NOT NULL,
[NEW_CITY] [char](35) NOT NULL,
[OLD_STATE] [char](29) NOT NULL,
[NEW_STATE] [char](29) NOT NULL,
[OLD_ZIPCODE] [char](11) NOT NULL,
[NEW_ZIPCODE] [char](11) NOT NULL,
[OLD_PYMTRMID] [char](21) NOT NULL,
[NEW_PYMTRMID] [char](21) NOT NULL,
[CurrUser] [char](100) NOT NULL,
[CurrTime] [datetime] NOT NULL,
CONSTRAINT [PK_SSC_LOG_PM00200_UPDATE] PRIMARY KEY NONCLUSTERED
(
[CurrTime] ASC,
[VENDORID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
[/sql]

I still need the vendor ID, which is the key field in the PM00200 table. But in the update table, I need the update time to also be part of the primary key, so that when a vendor row is updated more than once, each update operation has a unique primary key value.

Then, as you see, I save the before and after values for the data we considered important enough to track. And at the end of the row you’ll see, once again, the time stamp for the operation plus the account name credential that is making the update.

The trigger is a little longer also, but not complicated.

[sql]
CREATE TRIGGER [dbo].[SSC_TRIG_PM00200_UPDATE] ON [dbo].[PM00200] FOR UPDATE AS
BEGIN
INSERT INTO SSC_LOG_PM00200_UPDATE
(VENDORID
,OLD_VENDNAME
,NEW_VENDNAME
,OLD_VNDCHKNM
,NEW_VNDCHKNM
,OLD_VNDCLSID
,NEW_VNDCLSID
,OLD_ADDRESS1
,NEW_ADDRESS1
,OLD_ADDRESS2
,NEW_ADDRESS2
,OLD_ADDRESS3
,NEW_ADDRESS3
,OLD_CITY
,NEW_CITY
,OLD_STATE
,NEW_STATE
,OLD_ZIPCODE
,NEW_ZIPCODE
,OLD_PYMTRMID
,NEW_PYMTRMID
,CurrUser
,CurrTime)
SELECT
i.VENDORID
,d.VENDNAME
,i.VENDNAME
,d.VNDCHKNM
,i.VNDCHKNM
,d.VNDCLSID
,i.VNDCLSID
,d.ADDRESS1
,i.ADDRESS1
,d.ADDRESS2
,i.ADDRESS2
,d.ADDRESS3
,i.ADDRESS3
,d.CITY
,i.CITY
,d.[STATE]
,i.[STATE]
,d.ZIPCODE
,i.ZIPCODE
,d.PYMTRMID
,i.PYMTRMID
,SYSTEM_USER
,CURRENT_TIMESTAMP
FROM inserted i
JOIN deleted d
ON d.VENDORID=i.VENDORID
WHERE d.VENDNAME<>i.VENDNAME
or d.VNDCHKNM<>i.VNDCHKNM
or d.VNDCLSID<>i.VNDCLSID
or d.ADDRESS1<>i.ADDRESS1
or d.ADDRESS2<>i.ADDRESS2
or d.ADDRESS3<>i.ADDRESS3
or d.CITY<>i.CITY
or d.[STATE]<>i.[STATE]
or d.ZIPCODE<>i.ZIPCODE
or d.PYMTRMID<>i.PYMTRMID
END;
[/sql]

“Inserted” are the new values, and “deleted” are the old values. I join “inserted” and “deleted” on the key value VENDORID, because when multiple rows are updated in a single statement, “inserted” and “deleted” will generally both consist of multiple rows. And I use the where clause to filter out any updates that don’t change the important columns that we decided we want to track.

The log entries, at least the leftmost columns, look like this.

Sample Update Log Table

Sample Update Log Table

Between the previous post and this one, you’ve seen a complete example of how we log changes to the vendor master file. In a future post I’d like to discuss briefly which tables we’ve decided to log in this way, and why.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available