Month: March 2021

This post is part of a series on creating a simple audit for Microsoft Dynamics GP.

As I’ve shown over the last few posts, it is relatively easy to create a simple audit on a table in Dynamics GP, but this approach is not very scalable and requires someone to manually create the SQL triggers. For a client on a shoestring budget and who wanted to audit one table, this approach sufficed.

However, for a larger client who would want to audit more tables in more than one company and who would want to have non-technical users maintaining the audited information, I would recommend a solution like Assure Suite from Fastpath which I have implemented with a umber of clients previously.

What brought this approach to mind was a client had an issue with some incorrect data and we could not determine who or what was changing some data, so I amended this custom audit for the tables we needed to record for that issue. In this instance, there was no need for a full audit solution as it will only be in place temporarily while investigating a specific issue.

This post is part of a series on creating a simple audit for Microsoft Dynamics GP.

Once the audit table and triggers have been deployed, any changes made through the audit will be recorded and available for reviewing later to see who has been making changes and, more significantly, what was changed.

The easiest way of making this available to the client was to create a SmartList for them using SmartList Designer to select data from the new custom audit table. SmartList Designer can see either Dexterity tables or SQL views, but not custom SQL tables, I created a SQL view on the custom audit table, joining it to the Users Master (SY01400) table in the DYNAMICS table to get the username:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW uv_AZRCRV_Audit AS
SELECT
	['Audit'].RecordType AS 'Record Type'
	,['Audit'].RecordID AS 'Record ID'
	,['Audit'].UpdateType AS 'Update Type'
	,['Audit'].USERID AS 'User ID'
	,['User Master'].Username AS 'Username'
	,FORMAT(['Audit'].ChangeDateTime, 'yyyy-MM-dd') AS 'Change Date'
	,FORMAT(['Audit'].ChangeDateTime, 'HH:ss') AS 'Change Time'
	,['Audit'].OldData AS 'Old Data'
	,['Audit'].NewData AS 'New Data'
FROM
	ut_AZRCRV_Audit ['Audit']
LEFT JOIN
	DYNAMICS..SY01400 AS ['User Master']
		ON
		 	['User Master'].USERID = ['Audit'].USERID
GO

GRANT SELECT ON uv_AZRCRV_Audit TO DYNGRP
GO

This post is part of a series on creating a simple audit for Microsoft Dynamics GP.

With the table created to store the audited information, the second step is to create the required triggers on the Address Electronic Funds Transfer Master (SY06000) table. For an Vendor EFT audit there are three triggers required:

1. INSERT
2. UPDATE
3. DELETE

These triggers will record all new Vendor EFT information added as well as that which is amended or deleted. The client for which this audit was created only dealt with vendors in the UK and only ever set three fields in the EFT Bank window:

1. Bank Name
2. EFT Bank Code
3. EFT Bank Account

Additional fields can be added to the audit if other fields need to be stored.

As the Address Electronic Funds Transfer Master (SY06000) table holds EFT Bank information for customers as well as vendors, the Record ID has been set to include the Series. Strictly speaking, this was not necessary as the client did not store bank details for their customers in Dynamics GP as none were making direct debit payments.

The audited data is being trimmed and cast as varchar so the extra whitespace held by Dynamics GP due to the columns being chars are removed.

The first trigger creates the trigger which runs when data is inserted:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TRIGGER utr_AZRCRV_SY06000_AuditInsert ON SY06000 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'Vendor EFT'
		,CAST(i.SERIES AS VARCHAR(100)) + ' | ' + CAST(RTRIM(i.CustomerVendor_ID) AS VARCHAR(100)) + ' | '  + CAST(RTRIM(i.ADRSCODE) AS VARCHAR(100))
		,'Insert'
		,SYSTEM_USER
		,GETDATE_USER()
		,''
		,'BANKNAME = ' + CAST(RTRIM(i.BANKNAME) AS VARCHAR(100)) + ' | ' + 'EFTBankCode = ' + CAST(RTRIM(i.EFTBankCode) AS VARCHAR(100)) + ' | ' + 'EFTBankAcct = ' + CAST(RTRIM(i.EFTBankAcct) AS VARCHAR(100))
	FROM
		inserted i
GO

Continue reading “Simple Audit for Microsoft Dynamics GP: Create Triggers”

This post is part of a series on creating a simple audit for Microsoft Dynamics GP.

The first step in creating the audit is to create a table in the company database to hold the audited information. From the user point of view there was five pieces of information required:

1. Key for the vendor EFT being amended
2. User ID
3. Date/Time
4. Old Data
5. New Data

To make reporting easier and to add an element of future proofing, I also added two other pieces of information:

1. Record Type to record the type of information being audit; in this case Vendor EFT.
2. UpdateType to explicitly record whether the change was an INSERT, UPDATE or DELETE

The following SQL will create a table with the table with the seven columns mentioned above:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE ut_AZRCRV_Audit
	(
		RecordType VARCHAR(100)
		,RecordID VARCHAR(100)
		,UpdateType VARCHAR(10)
		,USERID VARCHAR(150)
		,ChangeDateTime DATETIME
		,OldData NVARCHAR(MAX)
		,NewData NVARCHAR(MAX)
	)
GO