is there a way to automatically update a column value when a new record is inserted into an oracle s-CodePudding

we have an api operation that enters a row into our table with a report_type=5, is there some sort of operation i can apply to the table to make it so whenever a record gets entered or pulled with a report_id=12 it returns the report_type as 4?

CodePudding user response：

As commented, trigger would do. Here's an example.

Sample table:

SQL> create table test
  2    (report_id   number,
  3     report_type number);

Table created.

Trigger:

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4    when (new.report_id = 12)
  5  begin
  6    :new.report_type := 4;
  7  end;
  8  /

Trigger created.

Testing:

SQL> insert into test (report_id, report_type) values (1, 13);

1 row created.

SQL> insert into test (report_id, report_type) values (12, 99);

1 row created.

SQL> select * from test;

 REPORT_ID REPORT_TYPE
---------- -----------
         1          13
        12           4    --> I inserted report_type = 99, but trigger modified it to 4
                          --> because report_id = 12

SQL>

CodePudding user response：

It's not clear which value you want to be STORED in the database: 12 (as entered), or 4 (as translated).

A trigger as proposed by another commenter would certainly be able to translate the value on insert or update.

If you want the original value to be stored, you'd need to set up a different column, that is derived based on the original one. An example swiped from an Oracle publication:

create table PERSON (
 (employee_id integer,
 employee_id_disp computed by
 SUBSTRING (CAST(employee_id   100000 as VARCHAR (6)) from 2)
 );

In your case, you might do something like

create table MYTABLE (
  somekey varchar(20) not null,
  entered_office int,
  display_office computed by decode(entered_office,12,4,entered_office)
);

Then, anything that needs to display the office number would need to use the display_office field, not the entered one. Any tool that does an insert into the table would also need to insert the entered_office field, as display_office is not updateable.