Removing Extender Data when deleting Transactions or Lines Revisited


David Meego - Click for blog homepageLast week, I tackled the subject of orphaned records in the Extender tables when the parent records were deleted and the Extender data was left behind.  If you have not read this post the link is below:



Removing Extender Data when deleting Transactions or Lines


If you read through the comments, you will see that Konrad Berger (an Australian Partner Consultant) mentions the idea of checking the Open (or History) table to see if the transaction has been moved to the next stage (ie. posted) rather than deleted. 


The concept works because most of the posting code in Microsoft Dynamics GP adds the records to the Open (or History) tables before it removed the old record from the Work table.  This gives a SQL table trigger the ability to tell the difference between a record being deleted by the user and a record being posted.


So this post will take the triggers attached to the previous post and modify them to use this concept. The updated triggers are shown below and also are attached to this post.  These updated triggers can be used in a live system and do away with the need for the Visual Basic for Applications (VBA) code in the previous post.


Extender Window Example – using IVTRF_1 & IVTRF_2


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10000_DELETE_IVTRF_1]’) and OBJECTPROPERTY(id, N’IsTrigger’) = 1)
drop trigger [dbo].[IV10000_DELETE_IVTRF_1]
GO


SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO


CREATE TRIGGER [IV10000_DELETE_IVTRF_1]
ON [dbo].[IV10000]
FOR DELETE
NOT FOR REPLICATION
AS


DECLARE @ExtenderID char(15)


select @ExtenderID = ‘IVTRF_1’


SET NOCOUNT ON


BEGIN



DELETE A FROM EXT00101 A — Strings
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D 
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)



DELETE A FROM EXT00102 A — Dates
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D 
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)



DELETE A FROM EXT00103 A — Numbers
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)



DELETE A FROM EXT00104 A — Times
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)



DELETE B FROM EXT00100 B — Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30200 D
      where D.IVDOCTYP = 3 and D.DOCNUMBR = C.IVDOCNBR)


END


GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10001_DELETE_IVTRF_2]’) and OBJECTPROPERTY(id, N’IsTrigger’) = 1)
drop trigger [dbo].[IV10001_DELETE_IVTRF_2]
GO


SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO


CREATE TRIGGER [IV10001_DELETE_IVTRF_2]
ON [dbo].[IV10001]
FOR DELETE
NOT FOR REPLICATION
AS


DECLARE @ExtenderID char(15)


select @ExtenderID = ‘IVTRF_2’


SET NOCOUNT ON


BEGIN



DELETE A FROM EXT00101 A — Strings
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00102 A — Dates
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00103 A — Numbers
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
      where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00104 A — Times
JOIN EXT00100 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE B FROM EXT00100 B — Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)


END


GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO


 


 


Extender Detail Window Example – using IV TRANSFER FORM


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IV10001_DELETE_IV_TRANS_FORM]’) and OBJECTPROPERTY(id, N’IsTrigger’) = 1)
drop trigger [dbo].[IV10001_DELETE_IV_TRANS_FORM]
GO


SET QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS OFF
GO


CREATE TRIGGER [IV10001_DELETE_IV_TRANS_FORM]
ON [dbo].[IV10001]
FOR DELETE
NOT FOR REPLICATION
AS


DECLARE @ExtenderID char(15)


select @ExtenderID = ‘IV TRANS FORM’


SET NOCOUNT ON


BEGIN



DELETE A FROM EXT00181 A — Strings
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00182 A — Dates
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00183 A — Numbers
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00184 A — Times
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE A FROM EXT00180 A — Lines
JOIN EXT00185 B ON A.PT_Window_ID = B.PT_Window_ID and A.PT_UD_Key = B.PT_UD_Key
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)



DELETE B FROM EXT00185 B — Line Mapping
JOIN deleted C ON B.Key_Strings_1 = C.IVDOCNBR and B.Key_Strings_2 = C.LNSEQNBR
WHERE B.PT_Window_ID = @ExtenderID and not exists(select 1 from IV30300 D
     
where D.DOCTYPE = 3 and D.DOCNUMBR = C.IVDOCNBR and D.LNSEQNBR = C.LNSEQNBR)


END


GO
SET
QUOTED_IDENTIFIER OFF
GO
SET
ANSI_NULLS ON
GO


 


 


You will need to test the triggers for your window to be sure that the concept of checking the Open (or History) table does work for the particular posting code. You should also have the Table Link or SQL Trigger to cleanup when the records are removed from History.


Hope you find this useful. Thanks Konrad for the idea.


David

Updated Item Transfer Extender Data Example.zip

Comments (1)