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

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

Re: SSE 2008: Referring to Other Rows Being Added

Show all headers | View raw

Gene Wirchenko (genew@ocis.net) writes:
>      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?
 
In this case you will need a trigger. As for how to implement the trigger,
you could assume that the table is OK, and only count the values in inserted 
and deleted. But this means that if someone disables the trigger, or 
bypasses it with BULK INSERT, that you will never be able to correct the 
data again, why it may be better to look at all instances of the values in 
the table.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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