Deletes through Replication

I have an order header we'll call HEADER and detail we'll call DETAIL
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:

> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))