-
Recursive Update Trigger ON Tree Structure Data
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 at each level is updated (the trigger is fired only once at each level):
-------------------------
UPDATE tbl_valid SET expired=1 WHERE parent=0
-------------------------
What I want is that this statement will fire a recursive trigger that will update the records of all childrens at each levels.
Why does it happen this way? How can I get around the problem?
The recursive triggers option has been turned on.
Thank you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|