SQL Scripts for Microsoft Dynamics GP: Simple RMA Audit

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script is another variation of the simple audit I created, but this time it audits for changes to the RMA tables.

/*
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)
		,Username varchar(150)
		,ChangeDateTime DateTime
		,OldData NVARCHAR(MAX)
		,NewData NVARCHAR(MAX)
	)
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditUpdate ON SVC05000 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,d.RETDOCID
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'RATETPID = ' + CAST(RTRIM(d.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(d.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(d.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(d.EXCHDATE) AS VARCHAR(100))
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditInsert on SVC05000 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,i.RETDOCID
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		inserted i
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditUpdate ON SVC05200 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(d.RETDOCID AS VARCHAR(15)) + ' | ' + CAST(d.LNSEQNBR AS VARCHAR(20))
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'UNITPRCE = ' + CAST(RTRIM(d.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(d.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(d.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(d.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(d.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(d.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(d.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
			AND
				i.LNSEQNBR = d.LNSEQNBR
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditInsert on SVC05200 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(RETDOCID AS VARCHAR(15)) + ' | ' + CAST(LNSEQNBR AS VARCHAR(20))
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		inserted i
GO