Re: Problem with Trigger and function

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Fri, 30 Aug 2002 10:11:07 +0200
Message-ID: <dm9umu8ct2gjeiobkgagv37ba47cvp1n7c@4ax.com>

On Fri, 30 Aug 2002 09:14:08 +0200, "Lensman" <Lensman.D_at_gmx.net> wrote:

>>
>> > Can some Oracle expert help me, please?
>> >
>> > The task (two-parted) to be solved:
>> > two tables (orderposition, storage)
>> > in these the following columns (orderposition.quantity,
>> > orderposition.status, storage.stored, storage.reserved -> all number,
>> > orderposition.artnr = storage.artnr)
>> >
>> > a) this is the more easy part
>> > after inserting new data in orderposition, the orderposition.quantity
>should
>> > be added to storage.reserved
>> > (like 'storage.reserved + orderposition.quantity) and storage should be
>> > updated with this new value - all for orderposition.artnr =
>storage.artnr.
>> >
>> > b) this seems more tricky to me
>> > after updating data in orderposition old.quantity should be compared
>with
>> > new.quantity, if they differ a new variable (diff = old.quantity -
>> > new.quantity) should be created.
>> > then if orderposition.status is 'N' (new) diff should be subtracted from
>> > storage.reserved and the row updated with the new value
>> > if orderposition.status is 'D' (delivered) diff should be added to
>> > storage.stored and the row updated with the new value
>> >
>> > Hope I didn't made too many mistakes in my description so that perhaps
>an
>> > oracle crack out there can help my with my problem cause I'm only
>beginner

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 - 03:11:07 CDT