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..:)
