Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is immediate field level validation possible in T-Sql? Expand / Collapse
Author
Message
Posted Friday, September 26, 2008 11:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
On my web application, I want my users to have immediate feedback - and never get to put in invalid data - rather than wait until the whole record is finished, and then have to go back to that field. I think Triggers don't fire until you go to post the record, yes?

Will a UDF fire when they exit the field? Or does it have to wait until post back of the record?

Or is this something I do via an Ajax container in my app?

Thanks,

Bill Ross


"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #577041
Posted Friday, September 26, 2008 12:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 3, 2012 7:36 AM
Points: 127, Visits: 517
client side validation coupled with server side validation coupled with DB validation. Roll back the transaction if anthing is found wrong with it during the insert.
Post #577114
Posted Friday, September 26, 2008 1:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:42 PM
Points: 451, Visits: 364
Hi Bill,
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.
Good luck.
Post #577119
Posted Friday, September 26, 2008 1:09 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, Visits: 119
Thanks. That was very helpful info.

"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
Post #577124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse