SQL Server data/input validation


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 🙂

Best Solution

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.