I found one of the Jr. Developer writing trigger for the requirement where he wanted to make sure invalidate data does not enter in table column. I suggested him to write Check Constraint. Check Constraints are very handy to make sure all the data in the table is validated before it enters in the database.
Let us check constraint on over one of the table on postalcode table in database AdventureWorks database. Constraint will suggest that value which is larger than 11 character can not be inserted into the column. Once constraint is created, it can be tested by tring to insert value which is larger than 11 character. It will throw an error saying it can not be done as it violates constraint.
USE AdventureWorks
GO
ALTER TABLE Person.Address
ADD CONSTRAINT CK_Address_PostalCode
CHECK (LEN(PostalCode) < 11)
GO
UPDATE Person.Address
SET PostalCode = '12345-12345-123'
WHERE AddressID = 1
GO
Result Set:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_Address_PostalCode”.
The conflict occurred in database “AdventureWorks”, table “Person.Address”,
column ‘PostalCode’.
The statement has been terminated.
Reference : Pinal Dave (https://blog.sqlauthority.com)
