I have an app with a UI that allows a user to input a blank string, i.e. ''. These columns have Not Null attributes, but the blank string bypasses that catch, of course. This data is later sent to other apps that do not like the ''.

Is there a way I can trap the '' and change it to 'Unknown', at the same time leaving the Not Null attribute in place and allowing other valid data to pass through? Can I do this with a constraint, or am I limited to a trigger to manipulate the data?

TIA
Elizabeth

In the first place why don't you check that from the UI front? It is better that you don't let blank strings get submitted from the UI, than sending blank strings and then checking the same again at the backend for validity. Why do you have to hit the backend to validate data, when you know you should not let blank strings pass on to database.

I will not say constraints would help you do that.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

You could create a constraint that dissallows ''. I just created one that looks like this: ([CharColumn] <> '')

When I tried to insert '', it failed. This of course doesn't help you to change the blank to 'unknown' automatically. For that you could only use a trigger or some logic in the UI.

Peter
-------------------------
Work smarter, not harder

I would do this in the UI, or even better in the BO layer (business objects layer). Something like: just before saving, if TheField = '' then set TheField to 'Unknown'. Also, i would add a constraint for not allowing empty values for TheField column. This ensures that the column will not contain empty values, even if the values are not inputed through your app that does the replacing of '' with 'Unknown'. And so the other app that doesn't accept empty values, will not break.

I think the user should be aware of the fact that empty values get replaced with 'Unknown', so that when the user wants to filter the rows, will know it has to say [TheField] = 'Unknown' and not [TheField] = '' which will return 0 rows.

In fact i think the best solution is to have an initial value of 'Unknown' for TheField for all new rows, and the user will be able to leave it as is, and save the row, or to enter another value for TheField. Also there should be a broken rule for not accepting empy values for [TheField].

i'm sorry for my english...