Re: Complex Integrity Checking

Hi iulian,

In the row level insert, update trigger you can log somewhere (in package PL/SQL-table or in other table) changes you are making. Than in the stalement level triggers AFTER UPDATE, INSERT you may perform you checks and raise an exception if needed.
You cannot access modified table from row level triggers, but can do that from statement level trigger. And this is the only way to go. Of course, you can check it on Application level, but that's another story.

This is not a strait forward way for "checking for duplicate values". Try to re-state your requirements. Make use of additional calculated columns maybe, that you can to use regular unique constraints of Oracle. Perhaps, function-based indexes could help you.

HTH,

--
Alexandre



> Hi guys. Here's my problem.

> I want to check the new values (when inserting&updating a table) against

the


> ones in the existing rows. Something like checking for duplicate values,

but


> using a unique constraint doesn't suit my needs.

> I think of a before insert&update trigger, wherein checking my condition

and


> raise a error if not valid. The problem is, in case of an update

statement,


> I get the mutating "ORA-04091 table <my table> is mutating....".

> I read a lot of doc but I didn't find any helping ideas. Can you give me

> some, or maybe a new approach to this kind of problem?

> Thanks in advance!

>

> iulian

>

>

>

****************************************************************************
**


> The information contained in this communication is confidential and

> may be legally privileged. It is intended solely for the use of the

> individual or entity to whom it is addressed and others authorised to

> receive it. If you are not the intended recipient you are hereby

> notified that any disclosure, copying, distribution or taking action in

> reliance of the contents of this information is strictly prohibited and

> may be unlawful. Orange Romania SA is neither liable for the proper,

> complete transmission of the information contained in this communication

> nor any delay in its receipt.

>

****************************************************************************
**


>

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author:

>   INET: Iulian.ILIES_at_orange.ro

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).

>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alexandre Gorbatchev
  INET: alexandre.gorbatchev_at_avermann.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).