Re: Combining Tables in Dev2000 Forms

Alan,

What the others have posted is not completely correct. There are ways in forms to do exactly what you want. If you only want to query the data, create a view that joins the tables for you ( as suggested by the others posters). If you want to be able to update the data, this is a bit tricky, but you can do it (regardless what the others have said).

If you need to update a block based on the join-view, first you will base the block on the view, but you will need to create some Transactional Triggers. This will work as long as the view does not obscure the source of the data, i.e. does not use group by (for the most part). If it is a simple join, then this will work just great.

Transactional Triggers "intercepts" certain calls to the database. Normally, Forms dynamically creates the SQL statement to that do the updates, inserts, locks, deletes, etc.. for you. By creating these ON-% triggers, Forms turns over the control to you in these triggers.

The way this logic works is that you will create the transactional triggers that intercepts the DML statement, then calls a stored procedure or packaged procedure to pass the contents of the rows being manipulated to the database, then the procedure will handle the update. You can call a local procedure on the form side if you prefer, but it requires more network traffic.

Here we go:
If you are going to update the data, then create the ON-UPDATE trigger at the block level.

Declare
  exception custom_exception;
  pragma exception_init(-20000, custom_execption); Begin
  update_proc(:field_a, :field_b, :field_c, :field_d); Exception
  when custom_exception then
    /* add error handling here to catch sever side raise_application_errors */
end;

Since the on-update trigger fires for every row being update (once the commit is issued), you don't need to worry about which row you are on. The stored procedure much contain the logic to know which columns go to what tables. In the example above, let's assume that field_a is the primary key,  field_b is from table B and field_c is from table C. Table C is a "child" table to B. Knowing this, then you could issue the following update statement:

    procedure update_proc (a in number, b in varchar2, c in varchar2, d in varchar2) is

    begin

      update table C
         set column_c = :c
      where foreign_key = :a;
   exception
      when others then raise_application_error(-20000, 'Failed to update C

    end update_prod;

Repeat logic for insert and delete statements. You must create an ON-LOCK trigger if you will be updating data by locking all appropiate rows and tables affect by the stored procedures updates.

Remember these ON-% triggers are defined at the block level.

If you have any questions, please email me at bprice2000_at_sprintmail.com.

Good luck.

Brian Price

Avinash Deshpande wrote in message <34A83D58.69EA_at_cisco.com>...
>Alan Schneider wrote:
>>
>> I am trying to have the first three instances of a column displayed in a
>> Form 4.5 form (easily done) along with its associated information from a
>> second table (seems to be quit a bit harder).
>>
>> What is the best way to combine the tables into a single "Block" so that
 I
>> can get the associated instances in the second table to show up next to
 the
>> columns in the first table. I have figured out how to get the second
 table
>> to display the associated data however it is just for one record at a
 time.
>> I have to click on the item of interest in the first table to have its
>> associated information to show up in the second tables text box.
>>
>> I can do this very easily in Access by creating a subform. But I cannot
>> figure out how to do this in Forms 4.5
>>
>> Please send any responses directly to aschneid_at_gte.net. Thanks in
 advanced!
>> Alan Schneider
>-------------------------------------------------------------------------
>Hi,
>U cannot combine tables to be a part of a single block. U need to create
>different blocks for diffrent tables. U may display those on the same
>canvas to get a feel of a single block.
>To retrive the data in the other block based on the values in the
>previous block , u can get it done in 2-3 ways.
>
>1) U can create a relation between those blocks. So when u execute a
>query in the first block, a related query will be fired in the other
>block too.
>
>2) U can CODE in the Post-query & Post-change triggers setting the
> SET_BLOCK_PROPERTY(block_name, DEFAULT_WHERE,' ')
>& then execute query.
>
>remember that those 2 triggers fire for each record.
>U can write the Post-query at block level & Post-change at field level.
>
>thanks,
>Avinash
Received on Fri Jan 02 1998 - 00:00:00 CET