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

A 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.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

PrintFriendly Email Linkedin Twitter Facebook

Related Posts
SQL View On Vendor EFT
Using SmartList Designer To Create Vendor EFT SmartList
Creditor Contains EFT Bank Details For A Different Creditor
VBA for Integration Manager to Insert Vendor EFT Bank And Email Addresses
SQL Trigger To Automatically Enable Email Documents In Purchasing – Remittance
SQL Stored Procedure to delete old Microsoft Dynamics GP Document Attachments
SQL Trigger To Automatically Enable Email Documents In Purchasing – Purchase Orders
SQL Trigger To Automatically Enable Email Documents In Purchasing – All