Re: mutating table

David Parkinson <dave_at_trueacid.freeserve.co.uk> schreef in berichtnieuws 8dt7o2$9lb$1_at_newsg4.svr.pol.co.uk...
> 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?
>
>

general idea to get around mutating table create a package
the package has a pl/sql table with rowids or primary keys for the rows affected
before statement trigger: initialize the pl/sql table after statement trigger for each row: capture the primary keys or rowids after statement trigger: process the pl/sql table.

More info on http://osi.oracle.com/~tkyte

Regards,

Sybrand Bakker, Oracle DBA Received on Sun Apr 23 2000 - 00:00:00 CDT