TRIGGER HELP in oracle

From: "Nicholas Mudie" <nmudie(at)chello(dot)com>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: TRIGGER HELP in oracle
Date: 2001-02-22 16:42:20
Message-ID: 99B219124BBED3119EF4009027CC89EF047BFDB0@nts_exch2.exchange.chello.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I'm trying to write an Oracle trigger and I hear this is the place I should
be. :)

trigger requirements:

A trigger that calulates the current volume PLUS the incoming volume.
Basically, I want to update the volume column based on the duplicate
billingid value that comes into the table.

i.e
I have

Billingid tbytesweekly

1234 9000

so, when new billingid comes into the table and it's 1234 with volume 1099 I
want the table to update the new volume of that billingid..SHOULD NOW =
10099 ;)

I have written this but it doesn't work, is there an easier way to do it????

CREATE TRIGGER upd_vol
BEFORE INSERT ON xact
FOR EACH ROW
DECLARE
totvol NUMBER;

BEGIN
SELECT SUM(tbytesweekly)
INTO totvol
FROM xact
WHERE billingid = :new.billingid

BEGIN
INSERT INTO xact
values (:new.billingid, totvol);

EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE xact
SET tbytesweekly = totvol
WHERE billingid = new:billingid;

END;
COMMIT;
END;
/

THANKS..:)

Browse pgsql-admin by date

  From Date Subject
Next Message Kajetan Kazimierczak 2001-02-22 16:44:56 Re: Re: what means "INSERT xxx yyy" ?
Previous Message Nicholas Mudie 2001-02-22 16:40:45 TRIGGER HELP in oracle