Si,

Thanks. To catch changes to existing data also, make it FOR INSERT,
UPDATE.

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-x29541701.[Email address protected] 07/31/07
12:47 PM >>>
Stephen you're a star thank you :-)

si

-----Original Message-----
From: Stephen Davey
[mailto:mssqldba-ezmlmshield-x2830580.[Email address protected]
Sent: 31 July 2007 17:39
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


---------------------------------------------------------------------
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