Re: [NOVICE] numericOnly trigger

From: Derrick Betts <list(at)blueaxis.com>
Date: Sat Sep 22 2007 - 10:28:00 EDT

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

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings Received on Sat Sep 22 10:30:17 2007