Thread: a rule question

I have two tables one is called "inventory" and the other is
"inventory_usage". When I insert into inventory_usage info like number of
widgets used for a given order_number I would the in_stock entry in the
inventory to be adjusted. Here are the two tables and my current attempts at
the rule:

create sequence "inventory_id_seq" start 1 increment 1;
create table "inventory" (
       "inventory_id" int4 default nextval('inventory_id_seq') NOT NULL
unique,
       "in_stock" int4 default 0,
       "name" character varying not null,
       "description" character varying not null,
       "cost_per_unit" money,
       "vendor_code" character varying,
       "vendor_name"  character varying,
       "vendor_address_id" int4   references address (address_id),
       primary key ("name")
);
grant update on inventory  to flip;
grant select on inventory  to flip;

create sequence "inventory_usage_id_seq" start 1 increment 1;
create table "inventory_usage" (
       "inventory_usage_id" int4 default nextval('inventory_usage_id_seq')
NOT NULL unique,
       "inventory_id" int4   references inventory (inventory_id),
       "timestamp" timestamp default now(),
       "used" int4 not null,
       "printer" character varying,
       "real_lpi" real,
       "order_num" int4,
       primary key ("inventory_usage_id")
);
grant all on inventory_usage to flip;
grant all on inventory_usage_id_seq to flip;


create rule inventory_usage_insert_rule as
       on insert to inventory_usage
       do
    update inventory set inventory.in_stock=(select in_stock from inventory
where
inventory_usage.inventory_id=inventory.inventory_id)-inventory_usage.used
           where inventory.inventory_id=inventory_usage.inventory_id;


I have also tried:

create rule inventory_usage_insert_rule as
       on insert to inventory_usage
       do
    update inventory set new.in_stock=old.in_stock-inventory_usage.used
           where inventory.inventory_id=inventory_usage.inventory_id;

I think the second one would work if I put a in_stock column in the
inventory_usage table. But this does not seam like a slick solution.


Thank you
Brad Paul

--- Brad Paul <bpaul@carolina.rr.com> wrote:
> I have also tried:
>
> create rule inventory_usage_insert_rule as
>        on insert to inventory_usage
>        do
>     update inventory set
> new.in_stock=old.in_stock-inventory_usage.used
>            where
> inventory.inventory_id=inventory_usage.inventory_id;
>

 Try recoding it as :

  CREATE RULE inventory_usage_insert_rule AS
     ON INSERT TO inventory_usage
     DO
        UPDATE inventory
        SET in_stock = in_stock - NEW.used
        WHERE inventory.inventory_id =
NEW.inventory_id;

Hope this helps,

ludwig.


__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com