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:40:45 |
Message-ID: | 99B219124BBED3119EF4009027CC89EF047BFDAE@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..:)
From | Date | Subject | |
---|---|---|---|
Next Message | Nicholas Mudie | 2001-02-22 16:42:20 | TRIGGER HELP in oracle |
Previous Message | Peter Eisentraut | 2001-02-22 16:35:22 | Re: Compilation errors |