We help IT Professionals succeed at work.

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

Last Modified: 2012-05-08

I have a Product table containing columns:

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!
Watch Question

Distinguished Expert 2019

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.


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.

Distinguished Expert 2019
This one is on us!
(Get your first solution completely free - no credit card required)


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.


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.