We help IT Professionals succeed at work.

Need to Validate data in SQL Server Database...should I use a trigger

315 Views
Last Modified: 2012-05-08
Hi,

I have a Product table containing columns:
...
ID
BrandID
TypeID
StyleID
ProductName
IsFeatured
....

If there are multiple products with the same BrandID, TypeID, StyleID and ProductName one and only one of the records needs to have IsFeatured set to 1.  How can I enforce this at the DB level?

I am thinking that I will need to create seperate Insert, Update and Delete triggers on the table to validate the modification.  If the modification results in more-than-one or less-than-one product record having the same BrandID, TypeID, StyleID and ProductName fields with the IsFeatured column selected rollback the change.  

This is a SQL Server 2000 database.

Any advice greatly appreciated!
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
You can not in the manner you outlined.
You could by separating the isfeatured from the product table and creating a isfeatured relationship table
productID (ID from the product table)
...

Can two brands have the same TypeID, styleid?
I.e. if I have a styleID can I get a unique brand,type?

Using unique index on the product table with brandid,typeid,styleid will prevent duplicate entries.

Without seeing an example of the data, it is hard to say
you could use a
brand table -> type table -> style table
each brand can have multiple types
each type can have multiple styles.

Author

Commented:
Yes, everything can be the same except the ID and IsFetured columns.

So you can have data as follows:

ID, BrandID, TypeID, StyleID, ProductName, IsFeatured
------------------------------------------------------------------
1         1           1            1            Name1            0
2         1           1            1            Name1            0
3         1           1            1            Name1            1
4         1           1            1            Name1            0
5         1           1            1            Name1            0


So because the above records all share the same values for everything other than there PK one (and only one) of them should have a 1 in the IsFeatured column.

CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
We have many other fields in the table which I due to other applications using it I have limited ability to change.

i have not received other responses so will rephrase th question with more detail.  Thank you for your help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.