Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL Trigger To Delete EFT Bank Information When The Vendor Is Deleted
December 15th, 2015 by Ian Grieve
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 (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). */ 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.