Clever Geek Handbook

SQL Server Data Validation / Data Entry

I am considering adding validation to some insert and update statements for MS SQL Server 2005. I want to be able to validate values ​​inserted before they are written to the table - in this particular case, it is a whole field where the data must match the rule. So a simple circuit could be:

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

And the magicvalue is the one I'm going to check. However, this is a common "best practice" question when indexes and foreign key constraints are not appropriate. I guess I'm not sure if triggers or constraints are the appropriate way or if there are other ways.

An example is optional :)

0


source to share


2 answers


It depends on how difficult the check is. If you can do it in a constraint, that is usually more efficient than a trigger. However, triggers can handle more complex checks that constraints cannot.



0


source


Use restrictions for this

eg



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

)
+1


source






More articles:


All Articles