Re: Problem with Trigger and function

From: Lensman <Lensman.D_at_gmx.net>
Date: Fri, 30 Aug 2002 11:48:19 +0200
Message-ID: <3d6f3ecb_3@corp-news.newsgroups.com>

Thank You very much for Your help, with 2 minor adjustements thr trigger was working:

create or replace trigger orderposition_ariu after insert or update on orderposition for each row
diff number(3); --> field length must be defined begin
diff := ''; --> variaable mut be invoked inthe PL/SQLblock if inserting then
update storage
set reserved = reserved + :new.quantity
where artnr = :new.artnr;
else

    diff := :new.quantity - :old.quantity;     if diff <> 0 then

        if :new.status = 'N'
        then
          update storage
          set reserved = reserved - diff
         where artnr = :new.artnr;
        else
          if :new.status = 'D' then
          update storage
          set stored  = reserved + diff
         where artnr = :new.artnr;
         end if; -- status = 'D'
       end if; -- status = 'N'

   end if; -- diff <> 0
end if; -- inserting or updating;
end;
/

Lensman

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:dm9umu8ct2gjeiobkgagv37ba47cvp1n7c_at_4ax.com...
>
> create or replace trigger orderposition_ariu after insert or update on
> orderposition for each row
> diff number;
> begin
> if inserting then
> update storage
> set reserved = reserved + :new.quantity
> where artnr = :new.artnr;
> else
> diff := :new.quantity - :old.quantity;
> if diff <> 0 then
> if :new.status = 'N'
> then
> update storage
> set reserved = reserved - diff
> where artnr = :new.artnr;
> else
> if :new.status = 'D' then
> update storage
> set stored = reserved + diff
> where artnr = :new.artnr;
> end if; -- status = 'D'
> end if; -- status = 'N'
> end if; -- diff <> 0
> end if; -- inserting or updating;
> end;
> /
>
> Dead easy. Lesson one in using triggers.
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri Aug 30 2002 - 04:48:19 CDT