OraFAQ Forum: Forms » validate data in details block before inserting into database

Home » Developer & Programmer » Forms » validate data in details block before inserting into database (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

validate data in details block before inserting into database [message #653233]

Wed, 29 June 2016 13:47

lucky123
Messages: 12
Registered: June 2016

Junior Member

Is there a way to validate data before inserting into database in oracle forms??

Requirement is to not to allow to save the data if specific data for the item does not exist.

Ex: In the details block there is an item called name_code. user entered 4 records for that item name_code.

when the user click on save the record should get saved only if one of the 4 records has item_code value as 'OTHER'.

how can we achieve this requirement.Please suggest

Report message to a moderator

Re: validate data in details block before inserting into database [message #653243 is a reply to message #653233]

Thu, 30 June 2016 00:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

There are two basic triggers for validation: WHEN-VALIDATE-ITEM and WHEN-VALIDATE-RECORD. However, none of these can do what you are looking for as you want to validate the whole set of records. Therefore, you'll have to use a loop and navigate through all records in a form, searching for ITEM_CODE = 'OTHER'.

PRE-INSERT would be a good choice, only if it supported restricted built-ins (as you'd have to use NEXT_RECORD in the loop). However, as it does not, maybe KEY-COMMIT could be used instead. Unfortunately, you can avoid its execution; for example, if user wants to exit the form, he'll be asked if he wants to save the changes. If he says "Yes", records will be saved without KEY-COMMIT trigger being executed.

Hopefully, there are other options and someone will suggest them. I can't think of any currently ...

Report message to a moderator

Re: validate data in details block before inserting into database [message #653248 is a reply to message #653243]

Thu, 30 June 2016 02:52

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

As far as I'm aware there is no way to do this. As LF says such a check would have to be done at the point of saving and the only relevant trigger - KEY-COMMIT - can be bypassed.
Why exactly does one of the four records have to have that value?

Report message to a moderator

Re: validate data in details block before inserting into database [message #653274 is a reply to message #653248]

Thu, 30 June 2016 13:58

lucky123
Messages: 12
Registered: June 2016

Junior Member

Hello ,

Thank you for the replies.Issue resolved.I used global variable in WHEN_VALIDATE_ITEM to store the item value status.And checked the global variable value in PRE_INSERT.

Report message to a moderator

Re: validate data in details block before inserting into database [message #653300 is a reply to message #653274]

Fri, 01 July 2016 03:15

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

So you set the global variable when one of the records is set to OTHER and only allow insert when once that happens?

Report message to a moderator

Re: validate data in details block before inserting into database [message #653462 is a reply to message #653300]

Wed, 06 July 2016 07:45

lucky123
Messages: 12
Registered: June 2016

Junior Member

yes.The global variable is set to 'Y' when record(one of the item in that record) is set to 'OTHER'.The record has three columns .so we need validate only one column data.In PRE_INSERT checking the lobal variable status and inserting data.

Report message to a moderator