Re: mutating table

A copy of this was sent to Gates <twofourblue_at_yahoo.com> (if that email address didn't require changing) On Tue, 10 Aug 1999 15:29:28 -0400, you wrote:

>I don't get it, I thought that I would get a mutating table error if I
>tried to update the table on which I created my trigger. In the
>following trigger I'm updating an different table yet I'm still getting
>the 'mutating table' error.
>
>Can anyone help?!?
>
>The trigger is on repair_equipment the update on repair_transaction.
>
>Thanx
>Gates
>
>
>CREATE OR REPLACE TRIGGER REPAIR1
>AFTER UPDATE OF returned_dt ON repair_equipment
>FOR EACH ROW
>DECLARE
> x NUMBER;
>BEGIN
> SELECT COUNT(*) INTO x FROM repair_transactions, repair_equipment
> WHERE repair_transactions.repair_trans_id = :new.repair_trans_id
> AND repair_transactions.repair_trans_id =
>repair_equipment.repair_trans_id
> AND Returned_Dt IS NULL;
> IF x = 0 THEN
> UPDATE repair_transactions SET closed_dt = SYSDATE
> WHERE repair_trans_id = :new.repair_trans_id;
> END IF;
>END;
>/

you have an update row trigger on repair_equipment. you are trying to read from repair_equipment in that update row trigger.

that is the error -- you cannot read from the table you are updating. that is the cause of the mutating table error. The select is causing it.

To see how to work around mutating table errors, you can visit the url in my signature below. there is a short howto there (as well as a pointer to the docs on how and why mutating table errors will ocurr)...

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 10 1999 - 15:09:35 CDT