Re: Oracle trigger (update another table)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 15 Feb 1999 17:46:47 GMT
Message-ID: <36c85d46.18996235@192.86.155.100>

A copy of this was sent to weiq_at_webworks.ca (if that email address didn't require changing) On Mon, 15 Feb 1999 16:47:44 GMT, you wrote:

>Hi, Friends
>
>I am new in Oracle database trigger,I would be very grateful if anyone could
>let me know how to update the stock amount in stock table when a customer
>make a order
>
>I wrote the trigger as the following, but it does not work
>-------------------------------
>create table stock (itemno number, stockAmount number, remainAmount
>number, primary key (itemno))
>
>create table orderstuff (orderno number, cust_Name varchar2(10), itemno
>number, unit number,primary key (orderno, itemno))
>
>create or replace trigger mytrigger
>before insert on orderstuff
>for each row
>DECLARE
> amount number;
>BEGIN
> update stock
> set stock.remainAmount := :old.stock.remainAmount - :new.orderstuff.unit
> where stock.itemno = :ordersuff.new.itemno;
>END;
you were close. looks like you are mixing up pl/sql and sql. The trigger would be:

create or replace trigger mytrigger
before insert on orderstuff
for each row
BEGIN

     update stock
        set remainAmount =  remainAmount - :new.unit
      where itemno = :new.itemno;

END;
/

you want to subtract :new.unit (the amount ordered) from the remainAmount in the stock table for a given :new.itemno.....

>--------------------
>
>Thanks and have nice day.
>
>Wei
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Feb 15 1999 - 11:46:47 CST