Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Trigger causing Mutating Table

Re: Database Trigger causing Mutating Table

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 2000/06/29
Message-ID: <962297071.4119.0.pluto.d4ee154e@news.demon.nl>#1/1

 CREATE TRIGGER order_status
/* note the change of after in BEFORE */  BEFORE UPDATE ON orders
 FOR EACH ROW
 BEGIN
    IF :old.order_dispatched_date IS NULL     AND :new.order_dispatched_date IS NOT NULL THEN      :new.order_status = 'DISP' ;
    END IF;  END;
/

Hth,

Sybrand Bakker, Oracle DBA

<japs1_at_my-deja.com> wrote in message news:8jfr9h$em7$1_at_nnrp1.deja.com...
>
>
> Can anyone tell me if the following is possible.
>
> If I have table ORDERS ...
>
> ORDER_NUMBER NUMBER
> ORDER_RECEIVED_DATE DATE
> ORDER_DISPATCHED_DATE DATE
> ORDER_STATUS VARCHAR2(30)
>
> I want to create a database trigger that updates the ORDER_STATUS
> depending on the state of the DATE columns in the table.
>
> For example, when ORDER_DISPATCHED_DATE is populated, i would like the
> ORDER_STATUS to change to 'DISPATCHED'.
>
> I have tried to do this with the following trigger, but I get a ORA-
> 04091 (table is mutating).
>
> CREATE TRIGGER order_status
> AFTER UPDATE ON orders
> FOR EACH ROW
> BEGIN
> IF :old.order_dispatched_date IS NULL
> AND :new.order_dispatched_date IS NOT NULL THEN
> UPDATE orders SET order_status = 'DISP' where order_number
> = :old.order_number;
> END IF;
>
> END;
>
> Can anyone help please?
>
> japs1
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 29 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US