Thread: numericOnly trigger

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.

Thanks,
Derrick

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

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?

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