Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Updating multiple tables from updatable report
Updating multiple tables from updatable report [message #459879] Tue, 08 June 2010 11:52 Go to next message
SlightlyRemoved
Messages: 1
Registered: June 2010
Junior Member
Hi, I'm new to APEX and SQL and need some help

I've got 2 tables, one for person (containing employee_id which is the primary key and position_id, which shows the position that the employee fills, and some others) and one for position (containing position_id as primary key and current_inc, which shows which employee is currently in that position, and various others). All variables are varchar2.
Now I've made an updatable report for the person table that can add and update the table without problems using a multi row update, but I also want if to update the value of current_inc in the position table if I were to assign a position to a certain employee in the person table.
I've though of using either a trigger or a procedure to do it but havn't been able to find satisfying examples online.
Any suggestions on how to do it (please include sql code)

Thanks
Re: Updating multiple tables from updatable report [message #459927 is a reply to message #459879] Wed, 09 June 2010 01:09 Go to previous messageGo to next message
Littlefoot
Messages: 21580
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is how I understood the problem. If I was right, Apex has nothing to do with it (except the UPDATE part which is done in Apex, but we can easily simulate it in SQL*Plus).

As you didn't provide test case, I tried to create one by my own. If it is wrong, well, we'll find someone to blame Smile OK, here it goes. First, create those tables and insert some records:
SQL> create table person
  2  (employee_id varchar2(10) primary key,
  3   position_id varchar2(10),
  4   some_other  number
  5  );

Table created.

SQL> create table position
  2  (position_id varchar2(10) primary key,
  3   current_inc varchar2(10),
  4   another     number
  5  );

Table created.

SQL> insert all
  2    into person (employee_id, position_id, some_other) values ('1', null, 100)
  3    into person (employee_id, position_id, some_other) values ('2', '20', 200)
  4    into position (position_id, current_inc, another)  values ('10', null, 1000)
  5    into position (position_id, current_inc, another)  values ('20', null, 2000)
  6  select * from dual;

4 rows created.

SQL> select * from person;

EMPLOYEE_I POSITION_I SOME_OTHER
---------- ---------- ----------
1                            100
2          20                200

SQL> select * from position;

POSITION_I CURRENT_IN    ANOTHER
---------- ---------- ----------
10                          1000
20                          2000

SQL>

Now, let's create a database trigger which will update the POSITION table once the PERSON.POSITION_ID column gets updated. As you didn't explain what is CURRENT_INC to be updated to, I chose current time:
SQL> create or replace trigger trg_pers
  2    after update of position_id on person
  3    for each row
  4  begin
  5    update position p set
  6      p.current_inc = to_char(sysdate, 'hh24:mi:ss')
  7      where p.position_id = :new.position_id;
  8  end;
  9  /

Trigger created.

SQL>

Finally, let's update a record in the PERSON table and see how it is reflected in the POSITION table:
SQL> update person set
  2    position_id = 10
  3    where employee_id = 1;

1 row updated.

SQL> select * from person;

EMPLOYEE_I POSITION_I SOME_OTHER
---------- ---------- ----------
1          10                100
2          20                200

SQL> select * from position;

POSITION_I CURRENT_IN    ANOTHER
---------- ---------- ----------
10         08:06:28         1000
20                          2000

SQL>

Is this what you are looking for? If not, could you explain it once again, but this time with some more details?
Re: Updating multiple tables from updatable report [message #459936 is a reply to message #459879] Wed, 09 June 2010 01:53 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
While creating updatable report using wizard, user is asked to select a table to be inserted or updated. But Apex gives option to select only one table.
That means you can update only one table by this.
If you want to update other table than selected one, use trigger as suggested by Littlefoot sir.

regards,
Delna
Previous Topic: Oracle_PHP_SUPPORT
Next Topic: How can i use LOV as tabs
Goto Forum:
  


Current Time: Wed Aug 12 02:31:54 CDT 2020