Removing Extender Data when deleting Transactions or Lines Revisited

Last 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

Dates of Interest

Social Networking & Syndication

Shortened URLs

Disclaimer

Removing Extender Data when deleting Transactions or Lines Revisited