We decided we wanted to track a few key pieces of data for Microsoft Dynamics GP. By “track” I mean to be able to tell who inserted, changed, and deleted anything, and by “we” I mean our CFO.

They've left a trail, Watson!

They’ve left a trail, Watson!

We decided to use triggers at the database level (and this time “we” means “I”).

For example, one of the things the CFO wanted to track was, who made what changes to the GP vendor master data. That’s contained in company table PM00200.

At the database level, there are 3 operations we have to track: insert, delete, and update. I made a separate table for each.

For each insert I just wanted to remember the vendor ID, which user performed the insert, and when. So I created a table named SSC_LOG_PM00200_INSERT for that purpose.

[sql]
CREATE TABLE [dbo].[SSC_LOG_PM00200_INSERT](
[VENDORID] [char](15) NOT NULL,
[CurrUser] [char](100) NOT NULL,
[CurrTime] [datetime] NOT NULL,
CONSTRAINT [PK_SSC_LOG_PM00200_INSERT] 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]

Then I added an insert trigger to the PM00200 table.

[sql]
CREATE TRIGGER [dbo].[SSC_TRIG_PM00200_INSERT] ON [dbo].[PM00200] FOR INSERT AS
BEGIN
INSERT INTO SSC_LOG_PM00200_INSERT
(VENDORID
,CurrUser
,CurrTime)
SELECT
i.VENDORID
,SYSTEM_USER
,CURRENT_TIMESTAMP
FROM inserted i
END;
[/sql]

Now whenever anyone adds a new vendor to the master data, we get an entry in the SSC_LOG_PM00200_INSERT table like this.

Sample Insert Log Entry

Sample Insert Log Entry

The delete log is almost identical. The table definition is the same, with a different name.

[sql]
CREATE TABLE [dbo].[SSC_LOG_PM00200_DELETE](
[VENDORID] [char](15) NOT NULL,
[CurrUser] [char](100) NOT NULL,
[CurrTime] [datetime] NOT NULL,
CONSTRAINT [PK_SSC_LOG_PM00200_DELETE] 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]

The trigger definition is similar.

[sql]
CREATE TRIGGER [dbo].[SSC_TRIG_PM00200_DELETE] ON [dbo].[PM00200] FOR DELETE AS
BEGIN
INSERT INTO SSC_LOG_PM00200_DELETE
(VENDORID
,CurrUser
,CurrTime)
SELECT
d.VENDORID
,SYSTEM_USER
,CURRENT_TIMESTAMP
FROM deleted d
END;
[/sql]

And the log entry for a deleted row is also similar.

Sample Delete Log Entry

Sample Delete Log Entry

So far so good. Tracking updates is a little more complicated but not much. We’ll look at update tracking in a future post. Hope these examples are helpful.

Leave a Reply

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

 characters available