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 > > > >