Re: Forms 4.5 - Block based on a View

In article <4mhg9s$avp_at_news.mel.aone.net.au>, simonc_at_oakton.com.au (Simon) says:
>
>G'day all,
>
>I have 2 tables:
>
>table_a (table_a_id number, desc_a varchar2(10), table_b_id number )
>table_b (table_b_id number, desc_b varchar2(10) )
>
>Originally I had a simple block based on table_a,
>with a post_query trigger to look up table_b.desc_b based
>on table_a.table_b_id.
>i.e. A block based on table_a with columns for:
> table_a_id, desc_a, desc_b
>
>The *User* now wants to have the block ordered by desc_b.
>
>Does anybody have ideas on a "nice" way to do this (I am sure someone has
>had to do it before!)?
>
>
>What I had in mind was to create a view:
>CREATE VIEW TABLE_A_VIEW AS
> SELECT TABLE_A_ID, DESC_A, DESC_B
> FROM TABLE_A, TABLE_B
> WHERE TABLE_A.TABLE_B_ID = TABLE_B.TABLE_B_ID;
>
>& then base my block on table_a_view. Of course this block wouldn't be
>updateable, so I would have to replace the update, insert, delete functionality
>manually by using the ON-INSERT, ON-UPDATE, ON-DELETE triggers.
>
>Then there is the problem of locking the record when the user trys to change it.
>Forms 4.5 doco says to use the ON-LOCK trigger to "lock underlying tables
>for non-updateable views" - but it doesn't say how!!
>
>
>My question is this - if what I have outlined above is the appropriate
>course of action (if not - what is a better one!), how to I simulate the
>locking & then updating of records at appropriate times???
>
>
>Any suggestions/comments etc appreciated.
>
>Thanks.
>
>-- Simon.
>

You'll have to deal with the locking using standard oracle locking mechanisms.

There are two solutions:

1. In your on-lock trigger you lock the table exclusive, which gives you a lock of all records in the table. (Code is lock table c in exclusive mode) Then your update-code would be a standard update using the keysof table_a and table_b.

This solution is however not satisfacting if locking is a problem - and when is locking not a problem?

2. The nice and best solution is to select .. for update of * in the two tables. you will have to create two non-visible char-fieldswith a length of at least 32 chars. Then your on-lock trigger should do two selects where you lock the record in the two tables and save the rowid. (you dont need to save the rowid but its no big deal!) The selects could look like this

select rowid from table_a where table_a_id=:block.table_a_id for update of *

The on-update trigger should then update the tables using the rowids, the code should be something like update table_a set .... where rowid=:block.table_a_rowid.

I hope this helps.

Rene Brisson
RBrisson_at_centrum.dk Received on Tue May 14 1996 - 00:00:00 CEST