trouble with recursive trigger

trouble with recursive trigger

Post by Bin Chen » Sat, 12 Feb 2000 04:00:00



Hi, gurus,

   Scenario:
   My table T1 structure like this
       {
            id          int;
            name    varchar(50);
            father    int;
        }
    sample data:
       {   1, Microsoft,0;
            2, Developing tools,1;
            3, Operating system,1;
            4, VB,2;
            5, VC,2;
            6,NT,3;
        }
        I try to use this table store a tree structure. Problem is when I
delete a record, such as first record, I must
delete all its descendent, such as 2,3,4,5,6. The easy way is create a
trigger to do the job.
        Because in SQL 7, the maximum recursive trigger is 32. So, when
record I want to delete which has more
than 31 descendents, my trigger will fail. Is there a better solution?

     Thanks

  Bin

 
 
 

trouble with recursive trigger

Post by Srikanth Garlapat » Tue, 15 Feb 2000 04:00:00


Hi Cheng,
    What you can possibly do is open a table for using as a semaphore.
Write the trigger with this pseudocode

1.  If semaphore is in locked state return
2.  If semaphore is unlocked,
        i)  Lock it.
        ii) Traverse through all the children in the tree and make a list of
them.
        iii) Delete all the children and grand children.
        iv) Unlock the semaphore.
3.  return

    I feel this operation should be done atomically.  Best way is to use a
stored procedure instead of automating this.

Srikanth G.


> Hi, gurus,

>    Scenario:
>    My table T1 structure like this
>        {
>             id          int;
>             name    varchar(50);
>             father    int;
>         }
>     sample data:
>        {   1, Microsoft,0;
>             2, Developing tools,1;
>             3, Operating system,1;
>             4, VB,2;
>             5, VC,2;
>             6,NT,3;
>         }
>         I try to use this table store a tree structure. Problem is when I
> delete a record, such as first record, I must
> delete all its descendent, such as 2,3,4,5,6. The easy way is create a
> trigger to do the job.
>         Because in SQL 7, the maximum recursive trigger is 32. So, when
> record I want to delete which has more
> than 31 descendents, my trigger will fail. Is there a better solution?

>      Thanks

>   Bin