SQL Scripts for Microsoft Dynamics GP: SQL Trigger on PO invoice Insert to Change GL posted Date

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 created a SQL trigger on the Posting Definitions Master Dup (SY00500) table to change the GL Posting Date to current date when a transaction is created.

This script was created for a client who was using eConnect to create purchase order invoices; unfortunately, we found that the GL Posting Date was being set to the same as the invoice date, but it needed to be the date the transaction was entered.

This script runs on insert and changes the date to the correct value.

CREATE TRIGGER utr_AZRCRV_UpdateGLPOSTDTOnInsert ON [dbo].[SY00500] AFTER INSERT AS
/*
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).
*/
	BEGIN
		UPDATE
			['Batch Master']
		SET
			GLPOSTDT = FORMAT(GETDATE(), 'yyyy-MM-dd 00:00:00.000')
		FROM
			SY00500 AS ['Batch Master'] --Posting Definitions Master Dup (SY00500)
		INNER JOIN
			inserted as I
				ON
					I.BCHSOURC = ['Batch Master'].BCHSOURC
				AND
					I.BACHNUMB = ['Batch Master'].BACHNUMB
				AND
					I.SERIES = ['Batch Master'].SERIES
		AND
			i.BCHSOURC = 'Rcvg Trx Ivc'
	END

GO