Database Trigger causing Mutating Table

From: <japs1_at_my-deja.com>
Date: 2000/06/29
Message-ID: <8jfr9h$em7$1@nnrp1.deja.com>#1/1

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