asked
Constraint Custom Message
How can we set custom message for user defined constraint for Table or any object for sql. ALTER TABLE SaleProduct ADD constraint chkshipDate CHECK(shipDate>=OrderDate) I want to show if user violating this rule during entry. like "Ship date must be later then order date' Please suggest
That looks like the sort of thing that should be handled by the UI before the data is sent back to the database.
I totally agree with Thomas Rushton that the best place would be to handle it at the GUI. This pre-validation is easier and save the round trip to database server and would save the resources. Hence a better performance over all. But if you have encapsulated the logic of INSERT/UPDATE in procedures, then you can have a workaround by comparing the dates in the procedure before INSERT/UPDATE. Then you can raise the customized error by using RAISERROR.
As both @Tomas and @Usman mentioned, the best place is the UI to handle this kind of checks. Except stored procedures if you really want to fire a custom message, there is another possibility by using TRIGGERS. You can file an exception in the trigger with whatever custom message you want. However once again, the best place is GUI. Even you do not want to handle all the things in GUI, you can catch exception related to violation of the constraints in GUI and show appropriate custom message.
Triggers probably come the closest to giving him what he is asking for. But I agree wtih everyone, this should be handled outside of the database in an area like the UI.
11 People are following this question.
