Problem with Cascading Triggers (SQL Server 2000)

[Jim]

I have created three tables (grandparent, parent, and grandchild). The GRANDPARENT
table contains a single record for each customer. The PARENT table would
have multiple child records to the GRANDPARENT table and finally the GRANDCHILD
table contains multiple child records to the PARENT table. I have created
two triggers to take care of cascading deletes (to maintain referential integrity).
The triggers live in the GRANDPARENT and PARENT tables as follows:

GRANDPARENT TABLE
------------------
CREATE TRIGGER CascadeGPDeletes ON [dbo].[grandparent]
FOR DELETE
AS
DECLARE @GP_No int
SELECT @GP_No=GrandParentNo FROM deleted
DELETE FROM Parent WHERE GrandParentNo=@GP_No


PARENT TABLE
------------------
CREATE TRIGGER CascadeParentDeletes ON [dbo].[Parent]
FOR DELETE
AS
DECLARE @P_No int
SELECT @P_No=ParentNo FROM deleted
DELETE FROM GrandChild WHERE ParentNo=@P_No


If I delete a PARENT record all of the GRANDCHILD records delete just fine.
If I delete a GRANDPARENT record it will delete all of the PARENT records
just fine if, and only if, the PARENT record does not have any GRANDCHILD
records.

The problem I am having is, if a GRANDPARENT record has multiple PARENT records
and those PARENT records has one or more GRANDCHILD records, the GRANDPARENT
trigger deletes only the first PARENT record and its GRANDCHILD records and
nothing more. It seems to quit cascading after that.

What do you think?

[David Satz]

why are using triggers when SQL Server 2000 has cascading deletes built into
it. You can setup the foreign keys to cascade the deletes for you.
--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Jim" <dropzone12@yahoo.com> wrote in message news:3c55819e$1@10.1.10.29...
>
> I have created three tables (grandparent, parent, and grandchild). The
GRANDPARENT
> table contains a single record for each customer. The PARENT table would
> have multiple child records to the GRANDPARENT table and finally the
GRANDCHILD
> table contains multiple child records to the PARENT table. I have created
> two triggers to take care of cascading deletes (to maintain referential
integrity).
> The triggers live in the GRANDPARENT and PARENT tables as follows:
>
> GRANDPARENT TABLE
> ------------------
> CREATE TRIGGER CascadeGPDeletes ON [dbo].[grandparent]
> FOR DELETE
> AS
> DECLARE @GP_No int
> SELECT @GP_No=GrandParentNo FROM deleted
> DELETE FROM Parent WHERE GrandParentNo=@GP_No
>
>
> PARENT TABLE
> ------------------
> CREATE TRIGGER CascadeParentDeletes ON [dbo].[Parent]
> FOR DELETE
> AS
> DECLARE @P_No int
> SELECT @P_No=ParentNo FROM deleted
> DELETE FROM GrandChild WHERE ParentNo=@P_No
>
>
> If I delete a PARENT record all of the GRANDCHILD records delete just
fine.
> If I delete a GRANDPARENT record it will delete all of the PARENT records
> just fine if, and only if, the PARENT record does not have any GRANDCHILD
> records.
>
> The problem I am having is, if a GRANDPARENT record has multiple PARENT
records
> and those PARENT records has one or more GRANDCHILD records, the
GRANDPARENT
> trigger deletes only the first PARENT record and its GRANDCHILD records
and
> nothing more. It seems to quit cascading after that.
>
> What do you think?
>

[Jim]

I did give that a try with no success. It seems I may not have implemented
that correctly. I will certainly give that another shot (foreign keys).
Thanks.
-----------------------------------------------------------------

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>why are using triggers when SQL Server 2000 has cascading deletes built
into
>it. You can setup the foreign keys to cascade the deletes for you.
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS
}
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Jim" <dropzone12@yahoo.com> wrote in message news:3c55819e$1@10.1.10.29...
>>
>> I have created three tables (grandparent, parent, and grandchild). The
>GRANDPARENT
>> table contains a single record for each customer. The PARENT table would
>> have multiple child records to the GRANDPARENT table and finally the
>GRANDCHILD
>> table contains multiple child records to the PARENT table. I have created
>> two triggers to take care of cascading deletes (to maintain referential
>integrity).
>> The triggers live in the GRANDPARENT and PARENT tables as follows:
>>
>> GRANDPARENT TABLE
>> ------------------
>> CREATE TRIGGER CascadeGPDeletes ON [dbo].[grandparent]
>> FOR DELETE
>> AS
>> DECLARE @GP_No int
>> SELECT @GP_No=GrandParentNo FROM deleted
>> DELETE FROM Parent WHERE GrandParentNo=@GP_No
>>
>>
>> PARENT TABLE
>> ------------------
>> CREATE TRIGGER CascadeParentDeletes ON [dbo].[Parent]
>> FOR DELETE
>> AS
>> DECLARE @P_No int
>> SELECT @P_No=ParentNo FROM deleted
>> DELETE FROM GrandChild WHERE ParentNo=@P_No
>>
>>
>> If I delete a PARENT record all of the GRANDCHILD records delete just
>fine.
>> If I delete a GRANDPARENT record it will delete all of the PARENT records
>> just fine if, and only if, the PARENT record does not have any GRANDCHILD
>> records.
>>
>> The problem I am having is, if a GRANDPARENT record has multiple PARENT
>records
>> and those PARENT records has one or more GRANDCHILD records, the
>GRANDPARENT
>> trigger deletes only the first PARENT record and its GRANDCHILD records
>and
>> nothing more. It seems to quit cascading after that.
>>
>> What do you think?
>>
>
>

[David Satz]

the FK would be defined like so:

FOREIGN KEY (ParentNo)
REFERENCES dbo.Parent ON DELETE CASCADE

"Jim" <dropzone12@yahoo.com> wrote in message news:3c55d37e$1@10.1.10.29...
>
> I did give that a try with no success. It seems I may not have
implemented
> that correctly. I will certainly give that another shot (foreign keys).
> Thanks.
> -----------------------------------------------------------------
>
> "David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
> >why are using triggers when SQL Server 2000 has cascading deletes built
> into
> >it. You can setup the foreign keys to cascade the deletes for you.
> >--
> >HTH,
> >David Satz
> >Principal Web Engineer
> >Hyperion Solutions
> >{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS
> }
> >(Please reply to group only - emails answered rarely)
> >-----------------------------------------------------------------
> >"Jim" <dropzone12@yahoo.com> wrote in message
news:3c55819e$1@10.1.10.29...
> >>
> >> I have created three tables (grandparent, parent, and grandchild). The
> >GRANDPARENT
> >> table contains a single record for each customer. The PARENT table
would
> >> have multiple child records to the GRANDPARENT table and finally the
> >GRANDCHILD
> >> table contains multiple child records to the PARENT table. I have
created
> >> two triggers to take care of cascading deletes (to maintain referential
> >integrity).
> >> The triggers live in the GRANDPARENT and PARENT tables as follows:
> >>
> >> GRANDPARENT TABLE
> >> ------------------
> >> CREATE TRIGGER CascadeGPDeletes ON [dbo].[grandparent]
> >> FOR DELETE
> >> AS
> >> DECLARE @GP_No int
> >> SELECT @GP_No=GrandParentNo FROM deleted
> >> DELETE FROM Parent WHERE GrandParentNo=@GP_No
> >>
> >>
> >> PARENT TABLE
> >> ------------------
> >> CREATE TRIGGER CascadeParentDeletes ON [dbo].[Parent]
> >> FOR DELETE
> >> AS
> >> DECLARE @P_No int
> >> SELECT @P_No=ParentNo FROM deleted
> >> DELETE FROM GrandChild WHERE ParentNo=@P_No
> >>
> >>
> >> If I delete a PARENT record all of the GRANDCHILD records delete just
> >fine.
> >> If I delete a GRANDPARENT record it will delete all of the PARENT
records
> >> just fine if, and only if, the PARENT record does not have any
GRANDCHILD
> >> records.
> >>
> >> The problem I am having is, if a GRANDPARENT record has multiple PARENT
> >records
> >> and those PARENT records has one or more GRANDCHILD records, the
> >GRANDPARENT
> >> trigger deletes only the first PARENT record and its GRANDCHILD records
> >and
> >> nothing more. It seems to quit cascading after that.
> >>
> >> What do you think?
> >>
> >
> >
>