check constraints

HI all,
I have a contraint on a table, the actual contriant does'nt matter for this
question, however it is something like "(len(ltrim([perildesc])) <> 0)'
which in conjucntion to not allowinh nulls, does not allow spaces (If there
is a better way let me know)
But what I want to know is, when the contraint is breached, an error message
is presented to the user, How can I trap for this particular contraint and
give a more meaningfull error messge. The error number is 547, which is a
generic contraint error message
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a contraint on a table, the actual contriant does'nt matter for
> this question, however it is something like "(len(ltrim([perildesc])) <>
> 0)' which in conjucntion to not allowinh nulls, does not allow spaces
> (If there is a better way let me know) But what I want to know is, when
> the contraint is breached, an error message is presented to the user,
> How can I trap for this particular contraint and give a more meaningfull
> error messge. The error number is 547, which is a generic contraint
> error message
There is not really any good way to do this. Of course, you can examine
the error text and extract the constraint name, and you could have a lookup
table that translates the constraint name to an error message.
The way I see it, the purpose of a constraint is not to trap errors
committed by the user, but to trap errors committed by the GUI/middle layer.
That is, the GUI is responsible for validating the user input.
Yes, this means that rules needs to be in two places, but for the GUI to
work well, this may be almost necessary. Say that you have one field A
that must be blank if B is filled in. The GUI should disable B as soon there
is data in A. Just permitting everything, and the hope that the database
validates it all, is not always a feasible strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks
This was what I thought to be the case.
Robert
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97B180CEC3B3Yazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> There is not really any good way to do this. Of course, you can examine
> the error text and extract the constraint name, and you could have a
lookup
> table that translates the constraint name to an error message.
> The way I see it, the purpose of a constraint is not to trap errors
> committed by the user, but to trap errors committed by the GUI/middle
layer.
> That is, the GUI is responsible for validating the user input.
> Yes, this means that rules needs to be in two places, but for the GUI to
> work well, this may be almost necessary. Say that you have one field A
> that must be blank if B is filled in. The GUI should disable B as soon
there
> is data in A. Just permitting everything, and the hope that the database
> validates it all, is not always a feasible strategy.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use an INSTEAD OF trigger and check perildesc in that and then use
RAISERROR to return your own user definied message and error number.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
> HI all,
> I have a contraint on a table, the actual contriant does'nt matter for
> this
> question, however it is something like "(len(ltrim([perildesc])) <> 0)'
> which in conjucntion to not allowinh nulls, does not allow spaces (If
> there
> is a better way let me know)
> But what I want to know is, when the contraint is breached, an error
> message
> is presented to the user, How can I trap for this particular contraint and
> give a more meaningfull error messge. The error number is 547, which is a
> generic contraint error message
> Thanks
> Robert
>|||Now there is an idea,
Thanks
RObert
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:#6FTJHTaGHA.4424@.TK2MSFTNGP02.phx.gbl...
> You could use an INSTEAD OF trigger and check perildesc in that and then
use
> RAISERROR to return your own user definied message and error number.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:uFouplRaGHA.3304@.TK2MSFTNGP04.phx.gbl...
and
a
>