Oh my! Phew! Hehe. Below is info from the "disgruntled development
DBA" and the "smart-aleck developer"(Oh my! Phew!)! BTW, are they
drinking Red Stripe (see last paragraph)? Hehe.


I can't stand it...

That "Insert thing" thread...

Aside from not solving the problem, the proposed trigger solution is
written incorrectly for a trigger. One should always code a trigger to
account for sets, not just single rows, which means no use of local
parameters like that.

The best method for this sort of requirement is usually an indexed view,
assuming the application's connection settings will tolerate it. A view
with a UNION operator cannot be indexed, however, so one must use other
tricks:

CREATE VIEW v_Simon1 WITH SCHEMABINDING AS
SELECT CASE WHEN c1 < c2 THEN c1 ELSE c2 END cx,
CASE WHEN c1 >= c2 THEN c2 ELSE c1 END cy
FROM dbo.Simon1
go
CREATE UNIQUE CLUSTERED INDEX IXV_Simon1 ON dbo.v_Simon1(cx,cy)

The OP also does not need his second index as uniqueness is commutative.
In fact, using the above indexed view obviates both of his indexes if
they're there just to enforce uniqueness.





Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Stephen Davey
[mailto:mssqldba-ezmlmshield-x2830580.[Email address protected]
Sent: Tuesday, July 31, 2007 12:39 PM
To: LazyDBA Discussion
Subject: Re: insert thing

Si,

Oooh, this hurt my little gray cells!!! The flaw in your thinking
below is that you want ICX_simon_left and ICX_simon_right to be equal
and they never will. I think you're going to have to create a trigger
to simulate the ICX_simon_right check. Something like the following:

CREATE TRIGGER Check_Insert
ON simon1
FOR INSERT
AS
declare @c2 as int
set @c2 = (select c2 from inserted)
if exists (select *
from simon1 S
where s.c1 = @c2)
begin
rollback
raiserror ('Cannot insert duplicate key row in object ''simon1''',
14, 1)
end

S. Davey (7/31)


MI DIT\Agency Services
7:30 A.M. - 4:00 P.M. ET
517.335.4237 (M- F CCC )


>>> "Loach Simon "
<mssqldba-ezmlmshield-x7767669.[Email address protected] 07/31/07 6:11
AM >>>
Hi All


I think my brains in advanced ready to go on holiday mode so some help
would
be appreciated


Got this table ..

drop table simon1
go

create table simon1

(
c1 int,
c2 int
)

alter table simon1
add constraint ck_test2 check (c1 <> c2)

CREATE unique INDEX ICX_simon_left ON simon1(c1,c2)
CREATE unique INDEX ICX_simon_right ON simon1(c2,c1)
go

insert simon1 (c1,c2)
values ('1','2')

insert simon1 (c1,c2)
values ('5','6')

insert simon1 (c1,c2)
values ('6','5')


The final "constraint" I want to be able to apply is if 5,6 exists
already
I want it to fail if the user tries to insert 6,5

Any wonderful suggestions gratefully received (go on ed you can do it
)


~si


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page