TRIGGER HELP in oracle

Поиск
Список
Период
Сортировка
От Nicholas Mudie
Тема TRIGGER HELP in oracle
Дата
Msg-id 99B219124BBED3119EF4009027CC89EF047BFDAE@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 по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Compilation errors
Следующее
От: "Nicholas Mudie"
Дата:
Сообщение: TRIGGER HELP in oracle