Implementing complicated constraints

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? Received on Tue Sep 28 2004 - 04:28:56 CDT