From: genew@ocis.net   
      
   On Tue, 12 Jul 2011 23:55:15 +0200, Erland Sommarskog   
    wrote:   
      
   >Gene Wirchenko (genew@ocis.net) writes:   
   >>      How do I force a condition based on multiple rows?   
   >   
   >Generally, you need to use a trigger.   
      
        I figured that was probably so, but wanted to check.   And your   
   response to my example is a nice specific case where it is not needed.   
      
   >> I want the necessary rows to be entered in a batch.  As a simple   
   >> example, what would be the coding of the constraint in this case   
   >> following?   
   >>   
   >>           create table Symmetric   
   >>            (   
   >>            a int not null,   
   >>            b int not null   
   >>            )   
   >>   
   >>      Constraint: If (x,y) is in Symmetric, then (y,x) is also in   
   >> Symmetric.   
   >   
   >Assuming that (a, b) is the primary key, this can be enforced with a   
   >constraint:   
   >   
   >CREATE TABLE Symmetric (   
   >   a int NOT NULL,   
   >   b int NOT NULL,   
   >   CONSTRAINT pk PRIMARY KEY (a, b),   
   >   CONSTRAINT fk FOREIGN KEY (b, a) REFERENCES Symmetric (a, b)   
   >)   
      
        That is elegant for the case I gave as an example.  I am,   
   however, looking for the more general approach.   
      
        Giving another example, suppose it must be that there are an odd   
   number of rows of a type of entry.   
      
             create table EvenSteven   
              (   
              pk varchar(10) not null,   
              contrivance int not null   
             )   
      
        In this case, there must be an odd number of rows for each value   
   of contrivance.  Suppose we have   
             pk    contrivance   
             A     5   
             B     7   
             C     6   
             D     5   
             E     5   
             F     5   
      
        Adding   
             G     6   
             H     7   
   would be an error (2 6's in total), but adding   
             G     6   
             H     7   
             I     6   
   would be fine.   
      
        Must I scan through the table and Inserted for each contrivance   
   value, or is there a better way?   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)