SQL Scripts for Microsoft Dynamics GP: Set New Vendor On Hold if EFT Exists

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

This script sets a new vendor to a status of on hold, if there are EFT details already present when the vendor record is inserted.

This was created for a client who had some problems due to a bug in Dynamics GP where EFT details weren’t deleted with the vendor card, which meant next time a vendor was created with the same Vendor ID there bank details for the previous record would still be present.

/*
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 [dbo].[utr_AZRCRV_EFTCreditorHold] 
   ON  [dbo].[PM00200]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @VENDORID CHAR(15)
	SELECT @VENDORID = VENDORID FROM INSERTED

	IF 
		@VENDORID IN
			(SELECT 
				VENDORID 
			FROM 
				SY06000 --Address Electronic Funds Transfer Master (SY06000)
			WHERE 
				VENDORID = @VENDORID)
	
		UPDATE 
			PM00200 --PM Vendor Master File (PM00200)
		SET 
			HOLD = 1 
		WHERE 
			VENDORID = @VENDORID
END