trouble with recursive trigger 
Author Message
 trouble with recursive trigger

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



Tue, 30 Jul 2002 03:00:00 GMT  
 trouble with recursive trigger
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.

Quote:

> 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



Fri, 02 Aug 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Recursive Definitions: Trouble or Bad news?

2. Recursive to non-recursive

3. newbie trouble: CoCreateInstance Error + IWebBrowser trouble...

4. .NET2002 C++ (x86) triggers ICE C1001 on Win2k (/Athlon 2.8)

5. ATL - Serious Memory leak triggering events

6. Events Delegates Triggers Targets

7. How can I trigger a Beep in csharp applications

8. SQL Server Triggers and C#

9. which undefined behavior it trigger?

10. How to write a trigger program in C

11. Trigger function to run one time per day?!?

12. OnKeyDown: Up, Down, Left, Right never triggered

 

 
Powered by phpBB® Forum Software