Re: mutating table

In article <8dt7o2$9lb$1_at_newsg4.svr.pol.co.uk>,   "David Parkinson" <dave_at_trueacid.freeserve.co.uk> wrote:
> I have a table with a self relationship and I want to put a trigger
 on the
> table that checks the parent (on the same table) before allowing any
 update
> to the table. I can't do this because Oracle gives me a mutating
 table
> error for the trigger. Is there any way to get round this or do I
 have to
> scrap the idea?
>
> I've thought of using a database procedure for the updates and taking
 update
> rights away from users but I would rather place the constraint (it's a
> constraint in the relational schema) in a trigger.
>
> Can anybody help?
>
>

See http://osi.oracle.com/~tkyte/Mutate/index.html.

You'll have to postpone the checking you are trying to do at the ROW level until an AFTER trigger. In order to do this, the row trigger will capture what rows changed -- the after trigger will process the validation.

It *has* to be this way -- the mutating table constraint is saving you from yourself. Lets say you had a table simply with:

ID      PARENT_ID     STATUS     .....other data ......
1       null          A
2       1             B

update T set status = 'C' where id in ( 1, 2 );

and your trigger checked that an update to a child row could only take place if the parent row was 'C'.

Now, depending *on the order in which the rows were processed* your row level trigger MIGHT see the parent row with a status of 'A', then again *it might* see it with a status of 'C' -- the row triggers are fired during the update, while its processing -- the table is changing. You could have the same exact data in the table and have an update come up with 2 different results, one time allowing the update and another not allowing it. Its even worse if you are querying the parent for data you are going to put into the child row -- its indeterminate what data would get there (if we let you that is)....

Another option is to create a view of the table, grant update on the view and place an INSTEAD of trigger on the view. Your instead of trigger will do the updates to the base table and can do whatever checking it wants before doing the update.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.