Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Trigger causing Mutating Table
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