TRIGGER HELP in oracle
| От | Nicholas Mudie |
|---|---|
| Тема | TRIGGER HELP in oracle |
| Дата | |
| Msg-id | 99B219124BBED3119EF4009027CC89EF047BFDB0@nts_exch2.exchange.chello.com обсуждение исходный текст |
| Список | 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..:)
В списке pgsql-admin по дате отправления: