Re: Overcoming "mutating table" error in a trigger

In article <36v5g1$e1_at_meaddata.meaddata.com> kcy_at_meaddata.com "Cheong Yu" writes:

> I have a column in a table where I want to place a UNIQUE
> "constraint". However, I want to allow "privileged" users to override
> this "constraint". That is, users will not be able insert a row if
> there is already a row in the table with a duplicate value for that
> column (unless that user is privileged).
>
> My first implementation of this feature was to have a before-row
> trigger to check the row being inserted to see if it the column
> value already exists in the table. However, this creates a "mutating
> table" error (see page 8-9 of Application Developer's Guide) because
> I'm SELECTing from a table I'm trying to INSERT into. The
> documentation suggests using a temporary table, a PL/SQL table, or a
> package variable (page 8-10). The temporary table might work but it
> seems like a lot of resources would be needed to copy the rows into
> the temp table. And I don't see how using a PL/SQL table or package
> variable would help in my case because I would need to "protect" the
> PL/SQL table or package variable from "unprivileged" users.
>
> Am I missing something obvious? Are there other ways to do what I
> want? Any suggestions would be appreciated.
>

 Try creating a view of the table and select from this in your trigger.  It works but is unsupported by Oracle. However, they are unlikely to remove  this loophole as so many people use it and have complained about mutating  tables

-- 
Stephen Lappin

+-----------------+
| +-------------+ |  Real Time Engineering Ltd.
| |  Real Time  | |  Capital House
| +-------------+ |  20 Park Circus
| Engineering Ltd |  Glasgow G3 6BE         Tel: +44 (0)141 332 9400
+-----------------+  Scotland U.K.          Fax: +44 (0)141 331 2509