> In article <cjbb40$b0d$1_at_news.caesar.elte.hu>, Agoston Bejo wrote:
>> 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?
>
>
> I am not sure if I understood 100% what you need, but if I did,
> ATXT belongs to table B, not to A.
>
> Rene
Please disregard my last post. I haven't 100% understood what you need....
Rene
--
Rene Nyffenegger
http://www.adp-gmbh.ch/
Received on Tue Sep 28 2004 - 07:44:50 CDT
