Day: December 15, 2015

SQL Trigger To Delete EFT Bank Information When The Vendor Is Deleted

Ian Grieve3 minute read7 Comments

Microsoft Dynamics GPA while ago I came across a bug in the Creditor (or Vendor for my American readers) Maintenance window where when a creditor is deleted the EFT information is not deleted; this has caused problems for a number of clients and I finally decided I needed to do something about it.

The result is a SQL trigger on the Creditor Master (PM00200) table; when a creditor is deleted the trigger runs and deletes all records in the Address Electronic Transfer Funds Master (SY06000) table are then deleted:

CREATE TRIGGER dbo.utr_AZRCRV_DeleteSY06000 ON dbo.PM00200 AFTER Delete AS
	/*
	Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
	*/
	DELETE
		['Address Electronic Transfer Funds Master']
	FROM
		SY06000 AS ['Address Electronic Transfer Funds Master']
	INNER JOIN
		deleted
			ON
				deleted.VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID
GO

This the avoids the possibility of a new creditor record being linked to a different creditors bank details. As always with a script (especially one which deletes information, make sure it is tested and you’re happy with how it works before releasing live.