XPost: comp.databases.oracle.server   
   From: gusz1@freemail.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?   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)