Day: March 9, 2021

Simple Audit for Microsoft Dynamics GP: Create Table

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPThis 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