Re: forms4.5 sorting - help needed

Johannes Kribbel <kribbel_at_datamed.co.at> wrote:

>I need to order the records of a tabular block by a non-basetable-item
>which is filled
>by a post-query trigger. I cannot use a view with a join because I need
>to update
>data.

<snip>

>Thanks
>Johannes Kribbel - kribbel_at_datamed.co.at - austria

You *can* update (and insert/delete) when a Forms 4.5 block is based on a view. A little bit of manual coding, though, is required. The SQL*Forms 3.0 Advanced Techniques manual describes how. I'm not sure if the comparable 4.5 manual does or not. The same technique, though, applies to either version. I would bet that Oracle also has a bulletin describing the technique

In short, you will need to code an ON-LOCK trigger, as well as the appropriate DML triggers (ON-UPDATE/ON-INSERT/ON-DELETE). The ON-LOCK trigger will lock the row being UPDATE/DELETED via a "SELECT ... FROM .. FOR UPDATE OF ... NOWAIT". The DML trigger(s) will then perform the appropriate DML -- "e.g. update emp set sal = :emp.sal where empno = :emp.empno".

Another alternative suggested in here before is to create a database function that returns the value to sort by, and reference the function in the ORDER BY property. This will avoid having to use a view; but, if you still need to *display* a non-base table field, you will still have to use the POST-QUERY trigger (and depending on the nature of the form, a WHEN-VALIDATE-ITEM trigger for populating the field).

I lean towards the view solution. If you do not have the manuals containing an example, or, if you run into problems, e-mail me and I will provide you an *explicit* example.

Larry G. Elkins
elkinsl_at_flash.net Received on Wed Aug 14 1996 - 00:00:00 CEST