Re: Implementation of a bag pattern using rules

Robert Creager wrote:

>When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000),
>Mark Gibson <gibsonm(at)cromwell(dot)co(dot)uk> 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.