Re: Implementing complicated constraints

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:<cjbb40$b0d$1_at_news.caesar.elte.hu>...
> Hi, I'm having trouble with implementing some constraints on the database
> level. An example:
>
> --Table A(AID, BID ref. B.BID, ATXT)
> --Table B(BID, CID ref. C.CID)
> --Table C(CID)
>
> upon insertion into or updating in A I would like to force that ATXT is
> unique with respect to CID, i.e.
>
> SELECT COUNT(*) FROM A,B,C
> WHERE A.BID = B.BID
> AND B.CID = CID
> AND CID = fn_get_cid_for_bid(:new.BID)
> AND A.ATXT = :new.ATXT
> AND A.AID <> :new.AID
>
> should be 0.
>
> This I cannot force with a check constraint since it is not allowed to
> contain subqueries.
> If I try to write a trigger that checks the above condition and raises an
> application error, then I always stumble upon the "table is mutating,
> trigger/function may not see it" -type error, since it involves a select on
> the table that is being changed at the time.
>
> However, it would be better to implement such a constraint on the database
> level, rather than scatter checks throughout the application.
>
> Is there a standard way solve this type of problem?

The fact that the ATXT column of A needs to be unique in relation to the CID value which does not appear in A indicates a relational design flaw in your tables.

Without changing the table design there are several options: 1- Perform the inserts/update via stored code that performs the validation up front avoiding the mutating table error

2- substitute a view for the table and use instead of triggers to perform the validation and redirect the DML

3- Use a combination of before and after, statement and row level triggers to work around the mutating table error. There are notes on this technique on metalink, but I have never managed to use the technique successfully to allow us to do what we needed in cases similar to what you want to do.

HTH -- Mark D Powell -- Received on Tue Sep 28 2004 - 16:23:49 CEST