Hi all!

I ran into a little problem with a trigger. I am using SQL Server 2000, the nested_trigger parameter is set to 1 (32 level of nesting) and I did an "alter database mydatabase set recursive_triggers on".

My table has a calculate field on it. When I update that field the after update trigger has to update the calculate field on other records of that same table. To know which record to update the trigger uses a cursor that loop from a reference table. The problem is that only one level is updated not all the chain.

Example:

Let say I have 3 records
R1
R2
R3

R2 is related to R1
R3 is related to R2

If I update the TOT field on R3, the trigger has to update the TOT field in R2 with the difference between old and new value of R3. Than the trigger should fire again to update R1 with the difference between old an new value of R3 and so on. Here is the problem, R2 is updated but not R1.

Any body has an idea?

Thanks you for your time.