Bind User Defined Messages to Rule Violations
-
Hello,
I am designing a Database.
I want to ensure all validations happen at the client and also at the server end.
My client validations in Javascript are done.
Now I am looking at the server side validations.
The best place to put them were in Rules/Constraints.
But the problem that I face is that I am unable to raise a user-defined error message when data violating a rule is entered.
For example, I have a unique constraint on the Name column of a Table.
Now only place this can be validated is on the server.
However I am unable to trap the error and do a raiserror of my own.
The only place I am able to do it is in an
Insert/Update Triggger on that table.
Here I have to validate all the data myself.
And raise the specific error and roll back the Transcation.
However this means that the row is inserted and then the rollback takes place.
Unlike in a Unique constraint where the data is validated before it is inserted.
So the basic question is can I bind the constraint to a user Defined message so that every time a violation of a rule happens a specific error is raised?
TIA,
RB
-
Which version of SQL Server are you using? I'm assuming 7.0, but I want to verify. Also, are you using ASP, Cold Fusion, etc?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
You could look at using instead of triggers in SQL2K to avoid the overhead of rollback. If you're doing your validation on the client, is it really critical that your db error messages be that informative? I know that doesnt answer your question, Im just curious on that point.
Andy
-
The constraints and such will still raise their own error. as far as I know, you cannot change this. You have to trap for it and replace the SQL error with your error.
You can always use a stored procedure and validate data like uniqeuness before insterting, but the overhead isn't worth it. Let SQL raise the error and build a good error handler.
Steve Jones
-
Hello,
The SQL Server version is 7.0
I am using ASP.
Yes, I would like the Error Message to be Informative to the max possible extent.
Hence this effort to push the limit of posibility.
If the Unique Constraint will always throw its own error, what is the next Best alternative?
As I mentioned earlier,
I have opted for validating the Data in the Insert/Update Trigger on the Table, with a Rollback for Invalid Data.
Seems to be the next best thing.
When I move to SQL2000 may be I could look at user-defined-functions.
I have yet to lay hands on a SQL 2000 m/c.
Thanx everybody for the participation.
Any further comments are welcome
RB
-
Hello,
I did not answer Andy Warren's Question.
Why do I need to have informative Server Side Error Messages if I am already doing client side validation?
The "Enable Javascript" is a Browser setting. Thus ,just like in my HTML I have to take care that it handles IE/NS issues, I need to take in my Javascript/ASP that I handle both JavaScipt Enabled/Disabled Browsers.
Any Comments?
Thanx for the Participation,
RB
-
There is a difference between doing server side validation and worry about the error messages. I agree with Steve - just map the sql errors to your own message. With some good coding, you could use the same text for errors regardless where raised.
That said, I know that doesnt help you solve the problem.
Andy
-
Hello ,
I ultimately implemented what Steve suggested.
I realised that
1) My Trigger option has a Rollback Overhead
2) The SP otion has a performance overhead.
The reason I was not happy with the constraint + Error handler architecture was that the eralier options could tell the User what data values caused the error rather than just through up an error.
But then I realised that I had the Data Values in ASP variables.
Hence my Error Messages to the User are informative as I want them to be though the constraint error message does not provide me that.
Thanx Everybody,
Regards,
RB
-
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply