SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Wednesday, May 10, 2006

Check Contraint question

SQL Apprentice Question
have the following check constraint


(isnull(patindex(('%[' + ' ' + char(9) + char(10) + char(13) + ']%'),[LicensePlateNumber]),0) = 0)


which works fine, throwing an error if those characters are entered. Is there a way to have it not throw an error, but rather just remove the offending characters if entered?

Celko Answers
No. Constraints are declarative and do not perform actions. I would
do this kind of thing inthe front end or in the inpout procedure.
Triggers will fire any time the table is touched and work on all rows,
so they can be a bit costly.

1 comment:

Max SQL said...

Triggers don't "work on all rows", in Microsoft SQL Server for which this problem was posted triggers fire for each INSERT / UPDATE or DELETE statement depending on how you configure the trigger, the inserted and deleted tables ONLY contain the rows being modified/inserted or deleted.