> I don't know if this can be done with RULES or i have to use
> FUNCTIONS.
>
> I have table categories with colums
>
> id,parent,name,cats
>
> this is some data
>
> 1 | 0 | cat1 | 3
> 2 | 0 | cat2 | 1
> 11 | 1 | cat3 | 1
> 12 | 1 | cat4 | 0
> 21 | 2 | cat5 | 0
> 111 | 11 | cat6 | 0
>
> cats colum mean how mani categories are inside some category,
> U can see that cat1 have 3 sub cats, one is also sub ob
> category cat3. This column is like totalsub category.
>
> Now i want create rule on insert. When i create new category
> i want that this cats would get automaticaly increased in all
> required rows.
>
> For example if i insert category
>
> INSERT categories (id,parent,name,cats) VALUES (112,11,cat7,0)
>
> I would like to increase by one in cat3 and also in cat1.
>
> I've make some rule to increase oly parent category but where
> i try create new rule to update all subs i have error msg
>
> query rewritten 10 times, may contain cycles
>
> I think i've done something wrong.
>
> Can somebody help me with this. What RULE to apply or maybe
> this have to be done with function
Uros --
You're getting the "may contain cycles" message because your UPDATE query
goes to your rule, which issues an UPDATE query, which goes to your rule ...
You could do this with a function w/o a rule, but you'd lose the natural
solution that rules would provide... other users wouldn't have to understand
anything to get the rule action to work.
How about:
Make a view of your table (CREATE VIEW xxx AS SELECT * FROM table)
Instead of putting the INSERT/UPDATE rules on the table, put them on the
view. Have the view INSERT and UPDATE rules calculate the correct values and
perform the INSERTs and UPDATEs directly on the table.
This way, your users can simply SELECT, INSERT, UPDATE, DELETE from the view
and the changes will get made to the table -- without the risk of the
recursive problem you're having now.
J.