After ID is updated, get a value from another table

oracleoracle-12cspatialtrigger

I have a roads table:

+---------+---------+
| ROAD_ID | MEASURE |
+---------+---------+
|       1 |    1000 |
|       2 |     100 |
|       3 |     500 |
|       4 |    9000 |
+---------+---------+

And I have a related linear referencing table called projects:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        101 |       1 |            0 |         50 |
|        102 |       1 |          100 |        500 |
+------------+---------+--------------+------------+
|        103 |       2 |            0 |        100 |
+------------+---------+--------------+------------+
|        104 |       3 |          100 |        200 |
|        105 |       3 |          400 |        500 |
+------------+---------+--------------+------------+

Users manually enter projects into application's attribute editing environment (more here).

The workflow for entering new projects is:

  1. The user creates a blank new record.
  2. The FROM_MEASURE has a default value of 0.
  3. The user enters the ROAD_ID manually.
  4. The user enters the TO_MEASURE manually.

You can't tell from the sample table, but 9 times out of 10, projects pertain to an entire road (not just a portion of the road). An example is PROJECT #103.

Rather than make the user manually enter the TO_MEASURE when imputing new projects, would it be possible to get these values automatically once the ROAD_ID is entered?

Example:

  1. The user creates a blank new record.

  2. The FROM_MEASURE has a default value of 0.

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |         |            0 |            |
+------------+---------+--------------+------------+

  1. The user enters the ROAD_ID manually.

  2. The TO_MEASURE is automatically retrieved:

+------------+---------+--------------+------------+
| PROJECT_ID | ROAD_ID | FROM_MEASURE | TO_MEASURE |
+------------+---------+--------------+------------+
|        106 |       4 |            0 |      9000  | <-- Magic!
+------------+---------+--------------+------------+

After ROAD_ID is updated, automatically get the related road's MEASURE value as the TO_MEASURE.

Best Answer

Doing this in the database is easy with a trigger. However, it's all well and good doing this in the database, but we have no idea how the application itself works or will deal with this (if at all). For example, the application may display what the user has entered rather than the value that the trigger has automatically inserted. I think you're going to have to deal with this in the front-end, which is something we probably can't help you with.

Anyway, here's how to write a trigger to do this.

Test tables and data:

create table roads
(
road_id number primary key,
measure number
);

create table projects
(
project_id number,
road_id number,
from_measure number,
to_measure number
);

insert into roads values(1,1000);
insert into roads values(2,100);
insert into roads values(3,500);

Trigger:

create or replace trigger projects_btrig
before insert on projects
for each row
declare
    v_measure number;
begin
    select measure 
    into v_measure
    from roads 
    where road_id = :new.road_id;

    :new.to_measure := v_measure;
end;
/

Test:

SQL> insert into projects (project_id, road_id, from_measure) values (1,1,0);

1 row created.

SQL> select * from projects;

PROJECT_ID    ROAD_ID FROM_MEASURE TO_MEASURE
---------- ---------- ------------ ----------
     1         1            0          1000

SQL>
Related Question