Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items

This post as been added as part of the series on creating a simple audit for Microsoft Dynamics GP, but wsn;t part of the original series.

I recently used the simple audit to add an audit to the Sales Customer Item Cross Reference (SOP60300) table to allow a client to keep an audit of changes to customer items. They wanted to keep track of all changes so this means three triggers are required on:

1. INSERT
2. UPDATE
3. DELETE

These triggers will record all customer items which are added, amended or removed. The Customer Items window contains a few fields, but the only ones with sensitive dta which needs to be audited are:

1. Customer Item Number
2. Customer Item Description

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

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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_SOP60300_AuditUpdate ON SOP60300 AFTER UPDATE AS

	INSERT INTO ut_AZRCRV_Audit

	SELECT

		‘Sales Customer Item Cross Reference’

		,CAST(RTRIM(d.ITEMNMBR) AS VARCHAR(30)) + ‘|’ + CAST(RTRIM(d.CUSTNMBR) AS VARCHAR(15))

		,‘Update’

		,SYSTEM_USER

		,GETDATE()

		,‘Customer Item Number = ‘ + CAST(RTRIM(d.CUSTITEMNMBR) AS VARCHAR(30)) + ‘ | ‘ + ‘Customer Item Description = ‘ + CAST(RTRIM(d.CUSTITEMDESC) AS VARCHAR(30))

		,‘Customer Item Number = ‘ + CAST(RTRIM(i.CUSTITEMNMBR) AS VARCHAR(30)) + ‘ | ‘ + ‘Customer Item Description = ‘ + CAST(RTRIM(i.CUSTITEMDESC) AS VARCHAR(30))

	FROM

		deleted AS d

	LEFT JOIN

		inserted AS i

			ON

				i.ITEMNMBR = d.ITEMNMBR

			AND

				i.CUSTNMBR = d.CUSTNMBR

GO

The second trigger creates the trigger which runs when data is updated:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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_SOP60300_AuditDelete ON SOP60300 AFTER DELETE AS

	INSERT INTO ut_AZRCRV_Audit

	SELECT

		‘Sales Customer Item Cross Reference’

		,CAST(RTRIM(d.ITEMNMBR) AS VARCHAR(30)) + ‘|’ + CAST(RTRIM(d.CUSTNMBR) AS VARCHAR(15))

		,‘Delete’

		,SYSTEM_USER

		,GETDATE()

		,‘Customer Item Number = ‘ + CAST(RTRIM(d.CUSTITEMNMBR) AS VARCHAR(30)) + ‘ | ‘ + ‘Customer Item Description = ‘ + CAST(RTRIM(d.CUSTITEMDESC) AS VARCHAR(30))

		,”

	FROM

		deleted AS d

GO

The third trigger creates the trigger which runs when data is deleted:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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_SOP60300_AuditInsert ON SOP60300 AFTER INSERT AS

	INSERT INTO ut_AZRCRV_Audit

	SELECT

		‘Sales Customer Item Cross Reference’

		,CAST(RTRIM(I.ITEMNMBR) AS VARCHAR(30)) + ‘|’ + CAST(RTRIM(I.CUSTNMBR) AS VARCHAR(15))

		,‘Insert’

		,SYSTEM_USER

		,GETDATE()

		,”

		,‘Customer Item Number = ‘ + CAST(RTRIM(i.CUSTITEMNMBR) AS VARCHAR(30)) + ‘ | ‘ + ‘Customer Item Description = ‘ + CAST(RTRIM(i.CUSTITEMDESC) AS VARCHAR(30))

	FROM

		inserted AS i

GO

Read original post Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items at azurecurve|Ramblings of a Dynamics GP Consultant