Re: Database Trigger causing Mutating Table

japs1_at_my-deja.com wrote:
>
> 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'.
>

Hi,

Try using a *before* update trigger instead, like this:

CREATE OR REPLACE TRIGGER my_trigger
BEFORE UPDATE ON ORDERS
FOR EACH ROW
BEGIN
   IF((:new.order_dispatched_date IS NOT NULL) AND

      (:old.order_dispatched_Date IS NULL))    THEN
      :new.order_status := 'DISPATCHED';    END IF;
END;
/

That should do the job

The trigger you wrote isn't allowed by Oracle, because it updates the trigger table... and that would fire the trigger again.... which would update the table again... and so on.

Regards
Steve Received on Thu Jun 29 2000 - 00:00:00 CDT