Re: [Q] Database Trigger mutating error...

jacquie_at_leia.endicott.ibm.com () wrote:

>I have what seems to be a simple problem:
 

> I would like to create a database trigger which insert
>a record to a historical table and delete the
>current record. It goes something like this:

> CREATE OR REPLACE TRIGGER CLOSE_WORK_ORDER
> AFTER UPDATE OF STATUS ON WORK_RECORDS
> FOR EACH ROW WHEN (NEW.STATUS = 'C')
> BEGIN
 
> /* Make sure a timestamp is present. */
> INSERT INTO WORK_HISTORY
> VALUES(:new.workOrderNo, :new.workOrderId, :new.status,
> ...
 

>
> DELETE FROM WORK_RECORDS WHERE WORKORDERNO = :NEW.WORKORDERNO;
 
> END;

>This however causes a mutating error (ORA-04091). This seems like a common
>thing to do (move a record from working to history). Do I need
>to do this programmatically or is there a way to handle this
>in a database trigger?
 

>Thanks in advance,
>Jacquie

It looks like your trigger is DELETEing the row that you just UPDATEd.

This is something you could do periodically (i.e. from the crontab).

# (initialize required environment variables) sqlplus << _EOF_ user/pwd
insert into work_history(...)
select ... from work_records where status = 'C'; delete from work_records where status = 'C'; commit work;
exit
_EOF_
exit 0 # shell exit

Note: you must NOT have a commit between the insert and delete statements.

--
Doug Henderson, Glen Coulee Consulting, Calgary, Alberta, Canada
[Sent using Free Agent 1.0]