Hi, I got stuck with the following problem and strangely couldn't find anything similar in the list:
Assume two tables: table: warehouse, columns: id, incoming, outgoing table: articles, columns: id, articles_in_stock All I want is to create a rule, that everytime when articles leave or get in the warehouse, the sum of articles is updated in articles database. Tried : CREATE RULE upd_articles AS ON UPDATE TO warehouse DO ALSO UPDATE articles SET articles_in_stock =SUM(NEW.incoming)-SUM (NEW.outgoing) WHERE articles.id=OLD.id; All I get is a "aggregate functions not allowed on update". So I tried to wrap it in a SELECT: CREATE RULE upd_articles AS ON UPDATE TO warehouse DO ALSO UPDATE articles SET articles_in_stock =(SELECT SUM(NEW.incoming)- SUM(NEW.outgoing) FROM warehouse WHERE NEW.id=OLD.id) WHERE articles.id=OLD.id; with the same result. What is the right way to achieve my goal? Or are rules the wrong instrument for it? Any help is kindly appreciated, Thanks, Sebastian ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
