I like this Mordechai, but I've tried to insert this into the update
trigger already in that table, and it doesn't work. First you need to
remove the AND part of the select. If I have other stuff that comes
after the ROLLBACK_TRANSACTION statement, it won't get run will it? It
doesn't seem to. Anyway, on an Update trigger, this code rolls back ALL
transactions. It let me change a true to false, but I can't change a
false to true. The select statement seems fine in QA (without the AND
section), so why is it always true?
------------------------------------------------------------------
Dan
Mordechai
Danielov
<mssqldba-ezmlms To
hield-x31233519. LazyDBA Discussion
[Email Address Removed] <[Email address protected]
DBA.com> cc
09/28/2006 12:10 Subject
PM RE: Conditional unique index?
I think you are right Jay, there does not seem to be a way for a
constraint
to check other rows in the table, but I ran into a problem with the
trigger,
may be you can see what it wrong:
create table test1386
(
KeyID int,
Person varchar(10),
Breed varchar(10),
Favorite varchar(10)
)
CREATE TRIGGER favorite_dog on test1386
FOR INSERT, UPDATE
AS
BEGIN
If exists (
select a.favorite from
test1386 a JOIN inserted b
ON a.person=b.person AND a.favorite=b.favorite
where a.favorite='TRUE'
)
ROLLBACK TRANSACTION
END
If you run
insert test1386 VALUES (1,'A','B','FALSE')
insert will work fine, but it favorite is set to 'TRUE' it will not be
inserted regardless of any other conditions...
what do you think?
-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x31531497.[Email address protected]
Sent: Thursday, September 28, 2006 2:48 PM
To: LazyDBA Discussion
Subject: RE: Conditional unique index?
Did you figure this one out yet? Can it be done as a check constraint?
This sounds like the type of rule that would be easy to enforce via
trigger.
Jay
From: Guzman
Sent: Thu 28-Sep-06 12:59
To: LazyDBA Discussion
Subject: RE: Conditional unique index?
That's the hint I was looking for. BOL makes it sound like this is my
answer, but the syntax examples are too simple. How do I write an
evaluation query for this?
Here is the query to show the count of favorite dogs I have, and in the
constraint I would want to limit it to 1 or less.
SELECT [Person], COUNT([Favorite]) AS [Count]
FROM [tblmyDogs]
WHERE ([Favorite]= 1)
GROUP BY [Person]
HAVING ([Person]= 'Dan')
So, the constraint field seems to be a variant on the WHERE clasue
theme. I just can't seem to fit my head around how to make this say
'where the count of the favorites per person is less than or equal to 1'
Here is a stab:
'COUNT([Favorite]) <=1 AND [Favorite]=1 GROUP BY [Person]'
------------------------------------------------------------------
Dan
Mordechai
Danielov
<mssqldba-ezmlms To
hield-x68153148. LazyDBA Discussion
[Email Address Removed] <[Email address protected]
DBA.com> cc
09/28/2006 09:13 Subject
AM RE: Conditional unique index?
Sounds like a candidate for a table check constraint, that will allow
only
one combination of person/breed/ and favorite=true
-----Original Message-----
From: Guzman [mailto:mssqldba-ezmlmshield-x92079819.[Email address
protected]
Sent: Thursday, September 28, 2006 11:55 AM
To: LazyDBA Discussion
Subject: RE: Conditional unique index?
In this case, just the three listed and rowguid for replication, oh, and
a table key. So:
KeyID, Person, Breed, Favorite
1, Dan, Cocker Spaniel, False
2, Dan, Lab, False
3, Dan, Bulldog, False
4, Dan, Great Dane, True
------------------------------------------------------------------
Dan
Mordechai
Danielov
<mssqldba-ezmlms To
hield-x61402050. LazyDBA Discussion
[Email Address Removed] <[Email address protected]
DBA.com> cc
09/28/2006 08:50 Subject
AM RE: Conditional unique index?
What columns do you have in this table?
-----Original Message-----
From: Guzman [mailto:mssqldba-ezmlmshield-x58686773.[Email address
protected]
Sent: Thursday, September 28, 2006 11:44 AM
To: LazyDBA Discussion
Subject: Conditional unique index?
Can you create a conditional unique index? An example: Each person can
have more than one breed of dog, but only one can be they're favorite.
So the primary key is Person/breed, but can I create a conditional index
that allows for only one Person/Favorite, but allows the rest to be
false? Something like 'WHERE count([Favorite])=1
Dan, Cocker Spaniel, False
Dan, Lab, False
Dan, Bulldog, False
Dan, Great Dane, True
I know there is a difference between better and easy. Better would be a
separate table of Favorites, with Person as primary, but an index would
allow me to save a step or three in combobox dropdown list creation
(adding a column to the dropdown list is way easier than creating
another subform, subquery and combobox with dropdown)
Dan
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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