Problem with Cascading Triggers (SQL Server 2000)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Problem with Cascading Triggers (SQL Server 2000)

  1. #1
    Jim Guest

    Problem with Cascading Triggers (SQL Server 2000)


    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?


  2. #2
    David Satz Guest

    Re: Problem with Cascading Triggers (SQL Server 2000)

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




  3. #3
    Jim Guest

    Re: Problem with Cascading Triggers (SQL Server 2000)


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

    >
    >



  4. #4
    David Satz Guest

    Re: Problem with Cascading Triggers (SQL Server 2000)

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

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center