Recursive Update Trigger ON Tree Structure Data 1

[jluost1]

My data structure is hierarchical tree structure like the following table tbl_valid:

parent child expired
0 1 0
1 2 0
1 3 0
1 4 0
2 5 0
2 6 0

In this table tbl_valid, I have an update trigger. The logic is:

Once a parent's expired is updated (true or false), update its children.

----------------------------------
SELECT @parent_id = parent_id FROM inserted

SELECT @expired = expired FROM inserted

@sql = 'UPDATE tbl_valid SET expired=' + CONVERT(char(1),@expired) + ' WHERE parent_id=' + CONVERT(varchar(3),@parent_id)

EXEC @sql
----------------------------------

By firing the trigger recursively, children at all levels (children and grand-children...) will be set to be the same as the starting parent_id (expired=true or false).

The problem:
When I fire the trigger by exec the following sql, only one record for each level is updated (the trigger is fired only once at each level):

-------------------------
UPDATE tbl_valid SET expired=1 WHERE parent=0
-------------------------

The recursive triggers option has been turned on.

Why does it happen this way? How can I get around the problem?

Thank you.

 

[jluost1]

Let me re-state the problem in short.

The situation:

I have an UPDATE trigger on a tree structure table. This trigger is supposed to execute an UPDATE statment that will update all its children. (So, it's a recursive trigger: update 0 will update 1 and 2; update 1 will update 3 and 4, update 2 will update 5 and 6; update 3 or 4 or 5 or 6 will update children of each...)

The problem:

At each execution level, only one child is updated, not all children at each level (update 0 will update 1 and 2, but just update children of 2, children of 1 is not updated, ...)

The answer:

???

 

[tlbroadbent]

You have an error in your trigger.

Your code is:
SELECT @parent_id = parent_id FROM inserted

The code should be:
SELECT @parent_id = child_id FROM inserted

I also suggest that you use a JOIN and eliminate the variables in the trigger to make the trigger more efficient. The following Update query is equivalent to what you are doing in your query with the correction I made.

UPDATE tbl_valid SET expired=inserted.expired
FROM tbl_valid INNER JOIN Inserted
ON tbl_valid.parent_id=inserted.child_id
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[jluost1]

You just made an excellent point in addition to correcting my mistake I made in simpliying the question.

I just tried using join in my update trigger. Now I get the following error:

Maximum stored procedure nesting level exceeded (limit 32).

The maximum level of my tree structure is less than 6. I guess when I call a recursive update trigger, the level that counts for SQL server is not the same as that of "Tree Structure". Maybe 2 items at the same level, but under different parents, are counted as 2 levels.

If so, is there a way to get around this?