Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    25

    Unanswered: Tree structures in SQL

    Hi,

    i'm writing a app in c# and have to store Trees in a Database.

    I'm working with Datasets for the exchange between the DB and the App.

    The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.

    But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:

    Not Possible:

    create Table tree (
    Id varchar Not null,
    ParentId varchar Not null,
    Constraint pk1 Primary Key (Id),
    Constraint fk1 Foreign Key (ParentId) references tree(Id)
    On Update Cascade
    On delete CAscade
    )


    Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action

    Greetz

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to use a trigger to do this.

    In the trigger, create a temporary table to store the primary key values of the records to be deleted. Insert the pkey of the target records into the table, and then loop through adding child pkeys of records already in the temporary table until no @@ROWCOUNT returns 0. Then link your temporary table back to your production table on the pkey values and perform your delete.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •