Re: numericOnly trigger

Oliver Elphick wrote:
> On Fri, 2007-09-21 at 17:39 -0600, Derrick Betts wrote:
>> Sean Davis wrote:
>>> Derrick Betts wrote:
>>>> I was wondering if anyone has built a trigger or etc. that verifies the
>>>> validity of an entry that is being posted to the database. I would like
>>>> to do the following:
>>>>    1.    Iterate through the column types in the table being updated
>>>>    2.    If the column type is numeric or real, or etc.,
>>>>        a. remove all the strings from the New.value
>>>>        b. set the New.value to the newly stripped value
>>>>     3.    Update the table with the modified values.
>>>>
>>>> The part I was hoping not to have to reproduce, if anyone has it and is
>>>> willing to share it, is the logic for iterating through the column types
>>>> and cleaning the specific values for update.
>>> I might be wrong, but I do not think your trigger will not actually fire
>>> if you try to do an update with text data in a column with a numeric
>>> datatype.  The type checking happens BEFORE a trigger fires, so you will
>>> simply get an error.
>>>
>>> Sean
>>>
>>>
>>>
>> If the trigger is a BEFORE UPDATE trigger will that not work?
>
> Well, the quick way to answer such a question is to try it!
> As soon as I thought about trying it, I realised that my trigger would
> be testing NEW.numeric_column, so it couldn't possibly work, because
> non-numeric data couldn't get into it in the first place.
>
> But I think your plan is fundamentally misconceived, because if the data
> is wrong, which is automatically the case if a numeric field contains
> non-numeric characters, you don't know what the right data is.  If
> someone enters "4w2", you intend to enter "42" - how do you know he
> didn't mean "432"?
>
That's a good question. It looks I may be relegated to solving the
problem in the user application. What I wanted was to change numbers
like $235,000.45 to 235000.45 for storage in the DB.
Thanks everyone for your insights.
Derrick