There are a whole lot of depends involved with this issue. First it depends to some extent on what technology you are using for your web app. But there are a couple of general statements that you can bank on. Nothing is going to happen in SQL until your web server makes a database connection and issues a query of some sort. The user can do a lot of things on the web page like tabbing, refresh, change focus, etc. that you really don't have control over and generally should not.
You can provide validation client side (for example by placing event driven code on the web form) and/or you can provide validation at your web server when the user/form posts back to the web server, and/or you can provide validation in the database server in the form of T-SQL logic, Constraints, etc.
I generally have developers place range type validation checks in the page logic. This includes things like date no earlier than 1900, phone number format, dollar limitations, numeric vs. character, etc.
Most of the business rule validation should be in the app layer on the web server. This includes things similar to inventory limits, work flow requirements (eg you can't check out until you have something in your cart), etc.
The database side checks should be limited to things that really require the database (to reduce turn-around). Duplicate handling, is a good example.
It is a worthy goal to say the user will never be able to input an invalid value or shift focus from a field with an invalid value. I found it an unobtainable goal, based on my users work flow and culture.
At one point we tried linking some database resident checking to specific fields (to provide a sort of intelli-sense feature) but we found the response time so erratic that we went back to partial field completion with a buttton.