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@cromwell.co.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.