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:
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