Re: Trigger to update triggering table

One way is through Materialized Views (8i). Read up on it.

Create Materialized View on medi_calls using query

    CREATE MATERIALIZED VIEW max_medi_calls     PARALLEL
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS

      SELECT tel_nr, prefix, NVL(MAX(call_nr),0) AS max_call_nr
      FROM medi_calls
      GROUP BY tel_nr, prefix

And use this view in your before INSERT.

Anurag Varma

"Kenneth Nyman" <knyman_at_hotmail.com> wrote in message news:<9l8e19$hks$1_at_news.kolumbus.fi>...
> Hi!
>
> I'm trying to create a trigger that retrieves information from the same
> table which activates the trigger.
>
> CREATE TRIGGER bi_increment_calls BEFORE INSERT
> ON medi_calls FOR EACH ROW
> BEGIN
> SELECT NVL(MAX(call_nr),0)+1
> INTO :new.call_nr
> FROM medi_calls
> WHERE tel_nr = :new.tel_nr AND
> prefix = :new.prefix;
> END;
> /
>
> On activation I get the following error:
>
> ORA-04091: table KENNY.MEDI_CALLS is mutating, trigger/function may not see
> it
> ORA-06512: at "KENNY.BI_INCREMENT_CALLS", line 2
> ORA-04088: error during execution of trigger 'KENNY.BI_INCREMENT_CALLS'
>
> There has to be a way to do this.. please help. :)
>
> /Kenny
Received on Wed Aug 15 2001 - 22:38:48 CEST