I've been trying to research a problem I have and the more posts I
read the more confused I'm getting.

I have 3 tables in a master-detail-detail situation:

ORDER
ORD_ID Int (PK)
OrdNum VarChar(8)
MonQty VarChar(1) //Bool

RECPT
REC_ID Int (PK)
ORD_ID Int
RecNum VarChar(8)
FK (ORD_ID) REFS ORDER(ORD_ID) ON DELETE CASCADE;

RECPT_DET
RECDET_ID Int (PK)
REC_ID Int
STK_ID Int
Qty Int
FK (REC_ID) REFS RECPT(REC_ID) ON DELETE CASCADE;

I also have a BEFORE DELETE trigger on RECPT_DET
which adjusts the QTY field in the STK table on STK_ID
but only if ORDER.MonQty = 'T' //true

This trigger queries the RECPT table (its master) to get the correct
ORD_ID, so that the value of MonQty can be determined.

Now,
From what I've read, (and from what I've tested/proven) the master is
deleted BEFORE the details are. As my trigger queries the master for
ITS master, but is returning NULL because the master is already
deleted, I'm unsure of how to have my design.

Whats the best design here?

A)
Do I have a BEF DELETE trigger on RECPT which manually deletes the
RECPT_DET records, and one on ORDER which manually deletes the RECPT
records? (allowing my triggers to function correctly as the masters
still exist at this stage)

B)
Or do I try to design my BEF DELETE trigger for my RECPT_DET table to
function without any references to it's parent(s)?
This will make things akward as it's the top master which has a field
to determine if QTY's should be adjusted at all.

Situation A seems to defeat the purpose of ON DELETE CASCADE, as I am
manually deleting the children in code anyway.

Situation B leaves me with redesigning my tables.

I'm not sure if there is a *cleaner* way to implement this...

Rgds,
-Ryan