Re: post-query and raise form_trigger-failure

Aleksandar Vasic wrote in message <38A16D5E.228343F7_at_ni.ac.yu>...
>HI everobody,
>I'm developing application using Forms4.5 and i'm experiencing
>problem with quering records:
>I have a database table block that should be queried, but with
>lots of complex restrictions which make it impossible to use where
>clause in block or pre-query trigger; so, i'm trying to use post-query
>trigger and raise form_trigger_failure when some complex conditions are
>not met.But, it seems that form_trigger_failure clears fetched record,
>but this record stays in a block as empty record !?
>Is there a way to solve this problem without changing that block into
>non-database table block, using cursor, etc..
>I'd appriciate any suggestion,
>Thanks...

Hi Aleksandar,

I'd say you're likely to have problems when using Post-Query for something it wasn't intended. Post-Query fires after the base-table record has already been fetched, and typically retrieves nonbase-table items associated with the fetched base-table items.
Form-Trigger-Failure wouldn't clear the record, it simply terminates the Post-Query trigger. Do you display any base-table items?

I suggest you modify the where clause.
If selection conditions vary to any great degree, the where clause can be loaded in a Pre-Query trigger with Set_Block_Property. The where clause may include sub-queries, eg. "and exists(select...)". Base-table columns should be referenced by using the full table.column name (this is not always necessary, since it depends on whether columns in your sub-query have the same name as your base table columns).

You can also extend the where clause by using a stored database function. The return value of the function can be used in a condition. If you pass a column name to the function it will be evaluated for each row, whereas a form block.item value is static once query execution begins. There is a very good paper on this subject at http://members.aol.com/jomarlen2/oraclesh.html It's called Functional Magic, and is available due to the generosity of John C. Lennon.
Make sure to take note of the use of the PRAGMA statment, otherwise it won't work (ie. you have to promise not to do any updates in the function). Received on Thu Feb 10 2000 - 04:11:36 CET