How to create trigger

Question

i'm using a sql server 2000. My Tree structured table is here

tItem
   ItemID int keyfield,
   ParentItemID int,
   Title nvarchar 200,
   ChildCount int,
   Status int

I need to calculate ChildCount and Status trigger. When i insert one item, parent item must calculate child count and status. 0 < status < 100

Calculate parent status that if the parent has 2 child, sum child status and then divide child count. example: 80+100/2 This operation recusive to root parent. for example:

Before :

Project Child=1, Status=80 
|-Module  Child=1, Status=80
| |-Control Child=0, Status=80

After insert Form item

Project Child=1, Status=90      // 3 Calculate child and status
|-Module  Child=2, Status=90    // 2 Calculate child and status
| |-Control Child=0, Status=80 
| |-Form  Child=0, Status=100   // 1 First. inserted row

This example has 3 level, Maybe my table tree structure level greater then 32

How to create this trigger?

Answer 1

I think you should be able to use this:

create trigger on tItem after insert or update as
if (inserted.ParentItemID is not null)
    update tItem set ChildCount = (select count(1) from tItem where ParentItemID=ItemID)
    where ItemID = inserted.ParentItemID
    -- calculate status here
go

That should, if I've still got all my marbles, fire and update the parent's ChildCount, which in turn will fire off the same trigger again and update the parent's parent's ChildCount until it runs out of parents to update.

However, I would strongly suggest you rather use a view, or a stored procedure, to calculate these dynamically - it's not a good idea to store data of this nature statically unless your table is going to be read heavily by other queries.

Answer 2

You should be able to call a UDF in your trigger for each row in inserted.

Alternatively, if the tree is manageable and not changing frequently, you can call an SP to recalc the entire tree after any insertion.

Also, in your example, why doesn't the root status change to 90 when its sole child's status has changed to 90?