SQL Server data/input validation

Question

I am considering adding validation on some insert and update statements for MS SQL Server 2005. I want to be able to check the values inserted before it is written into the table - in this particular case, it is an integer field where the data must conform to a rule. So a simple schema could be:

([id] [int] identity(1,1), [name] [nvarchar], [magicvalue] [int])

And magicvalue is the one I'm going to validate. However, it is a general question for the "best practice" when indexes and foreign key constraints are not suitable. I guess I am unsure whether triggers or constraints are the way to go, or if there are other ways.

An example is optional :)

Answer 1

Use constraints for this

e.g

CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int]  NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL CHECK (SalePrice > 4)

)

Answer 2

It depends on how complex the validation is. If you can do it in a constraint that is generally more efficient than a trigger. However, triigers can handle more complex validation which constraints cannot.