Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #504

Re: SSE 2008: Referring to Other Rows Being Added

Show all headers | View raw

On Tue, 12 Jul 2011 23:55:15 +0200, Erland Sommarskog
<esquel@sommarskog.se> 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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE 2008: Referring to Other Rows Being Added Gene Wirchenko <genew@ocis.net> - 2011-07-12 13:20 -0700
  Re: SSE 2008: Referring to Other Rows Being Added Erland Sommarskog <esquel@sommarskog.se> - 2011-07-12 23:55 +0200
    Re: SSE 2008: Referring to Other Rows Being Added Gene Wirchenko <genew@ocis.net> - 2011-07-12 15:37 -0700
      Re: SSE 2008: Referring to Other Rows Being Added Erland Sommarskog <esquel@sommarskog.se> - 2011-07-13 10:16 +0200
  Re: SSE 2008: Referring to Other Rows Being Added Gert-Jan Strik <sorrytoomuchspamalready@xs4all.nl> - 2011-07-13 19:39 +0200
    Re: SSE 2008: Referring to Other Rows Being Added Gene Wirchenko <genew@ocis.net> - 2011-07-13 12:14 -0700

csiph-web