Update and Insert trigger definition

[nico5038]

Hi Oracle experts,

I have an Oracle database (10g) with a table "WWW_PLUS" holding a field "ListName" defined as Varchar. It's used to store the number of minutes (don't ask me why) from an ASP page, but we weren't successfull in limiting the page inputfield to numbers only. (Some copy/paste actions add nonnumeric characters that can't be detected). Now we're running into trouble when performing calculations on the field.

Instead of manipulating the field from the page in ASP, I think defining a trigger could make sure that the Varchar field only gets numbers and nothing else.
As far as I know we need an INSERT and UPDATE trigger to change the string into a "pure numeric" string and store it in the tablefield. Here my knowledge stops. The samples on the web didn't gave me a clear view how to perform this task.

Can you help ?

Nic;o)

[amitpatel66]

Hi Oracle experts,

I have an Oracle database (10g) with a table "WWW_PLUS" holding a field "ListName" defined as Varchar. It's used to store the number of minutes (don't ask me why) from an ASP page, but we weren't successfull in limiting the page inputfield to numbers only. (Some copy/paste actions add nonnumeric characters that can't be detected). Now we're running into trouble when performing calculations on the field.

Instead of manipulating the field from the page in ASP, I think defining a trigger could make sure that the Varchar field only gets numbers and nothing else.
As far as I know we need an INSERT and UPDATE trigger to change the string into a "pure numeric" string and store it in the tablefield. Here my knowledge stops. The samples on the web didn't gave me a clear view how to perform this task.

Can you help ?

Nic;o)

Try this:

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1* SELECT REPLACE(TRANSLA TE('asedf12345w 2r3fer','123456 7890.,',' '),' ','') from dual
SQL> /

REPLACE(TR
----------
asedfwrfer

SQL>

SQL> ed
Wrote file afiedt.buf

1* SELECT REPLACE(TRANSLA TE(UPPER('asedf 12345w2r3fer'), 'ABCDEFGHIJKLMN OPQRSTUVWXYZ',' '),' ','') from dual
SQL> /

REPLACE
-------
1234523

SQL>
[/code]

[debasisdas]

I hope the previous post solves your problem. Please do post back.

[nico5038]

Sorry for the late reply, I was too busy with meetings and I forgot to activate the instant mail reply.

I've solved the Trigger problem by creating a trigger in TOAD and the main issue for me was how to use/detect the tablefield for the update.
Finally the code in my trigger is:

:new.listname = createnumberfromstring(:new.listname)

I'll try to publish the function code tomorrow.

Nic;o)