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.