Thread: Trigger function

Hello,
I'm trying to write a trigger that upon inserting a value in a table
another table is decremented. Something that could be used in an
inventory system, for example. I know it can be done easily outside
POSTGRES with PHP but in this case I would like the database to handle
it.


Example:

create table order (
    order_number integer not null default
nextval('order_number_seq'::text),
    itemid number integer references inventory,
    item_amt integer,
    cost numeric(3,2),
    constraint order_pk primary key(order_number)
);

create table inventory (
    itemid integer not null default nexval('itemid_seq'::text),
    onhand integer not null default 0,
    onorder integer not null default 0,
    desc varchar(30) not null,
    cost numeric(3,2) not null,
    constraint inventory_pk primary key(itemid)
);

Can you give me some direction on how to do this. In this case decrement
the inventory by the amount of the order for that particular item. I'm
not sure how to pass variables to triggers and/or functions.

Thank you


--
Kent L. Nasveschuk <kent@wareham.k12.ma.us>

Kent,

I suggest you read:

   http://www.postgresql.org/docs/7.3/interactive/plpgsql-trigger.html

I found it very useful myself, and it should answer (almost?) all your
questions.

Note that you will need to add either pl/pgsql or pl/TCL to your
database to do triggers.

The docs. for both languages are at:

   http://www.postgresql.org/docs/7.3/interactive/programmer-pl.html

        Andrew

Kent L. Nasveschuk wrote:

>Hello,
>I'm trying to write a trigger that upon inserting a value in a table
>another table is decremented. Something that could be used in an
>inventory system, for example. I know it can be done easily outside
>POSTGRES with PHP but in this case I would like the database to handle
>it.
>
>
>Example:
>
>create table order (
>    order_number integer not null default
>nextval('order_number_seq'::text),
>    itemid number integer references inventory,
>    item_amt integer,
>    cost numeric(3,2),
>    constraint order_pk primary key(order_number)
>);
>
>create table inventory (
>    itemid integer not null default nexval('itemid_seq'::text),
>    onhand integer not null default 0,
>    onorder integer not null default 0,
>    desc varchar(30) not null,
>    cost numeric(3,2) not null,
>    constraint inventory_pk primary key(itemid)
>);
>
>Can you give me some direction on how to do this. In this case decrement
>the inventory by the amount of the order for that particular item. I'm
>not sure how to pass variables to triggers and/or functions.
>
>Thank you
>
>
>
>