Re: [SQL] Implementation of a bag pattern using rules

[Mark Gibson]

Robert Creager wrote:

When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000),
Mark Gibson <[EMAIL PROTECTED]> confessed:
 

   CREATE RULE bag_abs AS ON INSERT TO bag_test
   WHERE
     EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)
   DO INSTEAD
   UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item;
   CREATE RULE bag_rel AS ON INSERT TO bag_test
   WHERE
     EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)
   DO INSTEAD
   UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
   

I'm no expert, just up early.  I believe both of these rules are
tripping. 
bag_abs is likely going first, then bag_rel, so bag_abs is inserting the
record,
then bag_rel is updating it.  You could verify this by deleting the two
rules,
then re-creating in the opposite order, and see if your inserted values
change. 

How would you expect the system to choose one of the two rules, which is
what
you apparently expect?
 

I probably didn't make this clear enough:

The system doesn't choose, YOU choose EITHER 'bag_abs' OR 'bag_rel' 
depending
on which behaviour is most appropriate for your application.
'bag_del' can be used in combination with either, to remove empty items.

The 'bag_abs'/'bag_del' rules work perfectly - I've provided them for 
feedback,
and hopefully others will find them useful.

It's only the 'bag_rel' rule that is giving me a headache.

Also, IIRC, rules are applied in alphabetical order, NOT the order in 
which they were created.

Cheers.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings