From: harder@daimi.aau.dk (Anders Harder)
Newsgroups: comp.databases.oracle
Subject: Re: Q: Forms and non-database fields
Message-ID: <1993Apr26.133223.19817@daimi.aau.dk>
Date: 26 Apr 93 13:32:23 GMT
References: <1993Apr25.170313.27180@dxcern.cern.ch>
Sender: news@daimi.aau.dk
Organization: DAIMI: Computer Science Department, Aarhus University, Denmark
Lines: 73


ezharkov@dxcern.cern.ch (Eugeny Jarkov) writes:

>I am using  "SQL*FORMS_VERSION = 03.00.16.04.02".
 
>My block contains a few non-database fields. I do query on non-database field
>using the technique which is described in "Advanced SQL*Forms Techniques".
>I am trying to find out a way to allow not only query, but input into these
>non-database fields.
 
>table T1 (ID number, F1, ...);
>table T2 (ID number, NAME char(10));
 
>block B1 with the base table T1 (so "NAME" is non-database field).
 
>ID      NAME            F1      ......
>____    __________      _____   ......
 
>I allow input into "ID" and "NAME". For "NAME" field I use
>"ON-VALIDATE-FIELD" trigger:
 
>        select ID from T2 where NAME=:B1.NAME
 
>I also use "POST-QUERY" trigger:
 
>        select NAME from T2 where ID=:B1.ID

Use the post-change trigger instead (as a field trigger) on the ID field. When
altering a value in a post-change trigger the validating will not fire. (I
think you need to have the validation-scope set to field)

Another possibility: select name into a temporary variable and use the copy
packaged procedure to copy the name into the field. The copy-procedure will not
trigger the validation either.

>The problem:
>        NAME is selected in the POST-QUERY trigger,
>        ON-VALIDATE-FIELD fires,
>        ID is selected in the ON-VALIDATE-FIELD,
>        block B1 is marked as CHANGED.
 
>The questions:
>        Is it possible to prevent ON-VALIDATE-FIELD from firing?
>        Is it possible to setup that "No changes to commit"?
>        Is there another technique to do this?
 
>Thanks!

Another way to do it is to make a view and then make the needed on-lock,
on-update, on-insert and on-delete triggers. It is more cumbersome to do, but
results in a faster form as the database is only queried once for each row and
not twice (or more).

What you need (and I need also) is a on-altered-by-user trigger.

If your name-coloumn isn't unique you will encounter a lot of other problems.
I have had a similar problem with displaying 2 'functionel dependent columns'
and allowing for updates in both. I usually 'solves' this problem by not
allowing user-update in the ID-field, but when validating the name-field I
allow an ID-entry (my post-change look something like: 'select id, name into
:id, :name from t2 where id like :name or :name like name' and further I handle
the exeption off too-many-rows with an 'auto list-of-values'). This is normally
not a big problem, as the ID and the NAME will not have confliction namespaces.

Hope this helps


-------------------------------------------------------------------------------
| Anders Harder (harder@daimi.aau.dk) I learn mostly from my failures...      |
| Comp. Sci. Dept.| Private address:  it isn't that I don't learn from my     |
| Aarhus Univ.    | Ydunsvej 12       successes...                            |
| DK-8000  Aarhus | DK-8230 Aabyhoj   but I have more failures than successes!|
| Denmark         | Denmark           (Rune T. Kidde)                         |
-------------------------------------------------------------------------------