how to make a trigger do more iterations

[Kobrowsky]

I have a MLM aplication and want to calculate POINTS for members. I have a trigger on the MEMBERS table that is triggered on UPDATE.
i'm only going to describe three fields in my table:

MEMBER_ID, SPONSOR_ID, BONUS, ACUMULATED_BONUS
(acumulated bonus is BONUS + ACUMULATED_BONUS from all members that have this member for sponsor. or: where SPONSOR_ID = MEMBER_ID)

when ACUMULATED_BONUS changes, my trigger does this: update TABLE set ACUMULATED_BONUS = ACUMULATED_BONUS + ((select ACUMULATED_BONUS from inserted) - (select ACUMULATED_BONUS from deleted)) where SPONSOR_ID = MEMBER_ID

example:
member sponsor
11 1
21 2
111 11

so when ACUMULATED_BONUS for member 111 is updated, it is also updated for 11, but that is it. i want it to be updated also for MEMBER_ID 1 and his sponsor and so on to the top level.

so the trigger should run for every update of the ACUMULATED_BONUS field, not just for the first one.

any ideas? or maybe an idea how to do it some other way if triggers are not the right way to do it.

 

[royjimenez]

CODE NAME PARENT
1 John 0
2 Ann 0
3 Luis 0
4 Nick 1
5 Abril 4
6 Carmen 3
....

I have done some data models with this structure. You can use it for designing trees.

if u are on the row Nick ( CODE 4 and PARENT 1 ) , you have to go through PARENT until it becomes 0, retrieving by CODE.

The NAME field is not important, it could be any value.

I hope it helps.

Regards.

The life is too short to cry and long enough to try it... God bless us.