What is the right PL/SQL for updating rows without a need to reinsert it?

Question

I new at using PL/SQL and I want the following: I have this table on Oracle SQLcl

create table Child (
id varchar not null,
name varchar not null,
gender varchar not null,
YearOfBirth number(4) not null,
YearsOfAge number(4) null,

CONSTRAINT Pk primary key (id)

);

And I want a PL/SQL (preferred anonymous) that update field of "YearsOfAge" by minusing 2020 from the "YearOfBirth" field. I could do that but my problem is that the table won't be updated until I insert the PL/SQL block again. So whenever I insert a new row, I have to insert my PL/SQL block again. I want to get the table updated whenever I insert/update a row, without a need to insert this block following a new row.

To be clearer, I just want to insert SL/SQL block one time after creating the table, then get the table's "YearsOfAge" updated whenever I insert/update/delete a row. So when I write "select * from Child;" I need to see the "YearsOfAge" with the new value that computed from subtracting 2020 from "YearOf Birth". My current PL/SQL is below:

begin
IF INSERTING THEN
update Child set YearsOfAge = 2020 - YearOfBirth;
ELSIF DELETEING THEN
update Child set YearsOfAge = 2020 - YearOfBirth;
ELSE
update Child set YearsOfAge = 2020 - YearOfBirth;
END IF;
END;
/

Answer 1

If you really need to store the age this way, some options are virtual columns, views, and triggers.

Virtual Column

With a virtual column, Oracle will automatically perform the calculation on the fly.

SQL> create table Child
  2  (
  3      id          number not null,
  4      name        varchar2(10) not null,
  5      gender      varchar2(10) not null,
  6      YearOfBirth number(4) not null,
  7      YearsOfAge  number generated always as (2020 - yearOfBirth) null,
  8      constraint pk_child primary key (id)
  9  );

Table created.

SQL> insert into child(id, name, gender, yearOfBirth) values(1, 'A', 'female'    , 1990);

1 row created.

SQL> insert into child(id, name, gender, yearOfBirth) values(2, 'B', 'male'      , 2000);

1 row created.

SQL> insert into child(id, name, gender, yearOfBirth) values(3, 'C', 'non-binary', 2010);

1 row created.

SQL> select * from child;

        ID NAME       GENDER     YEAROFBIRTH YEARSOFAGE
---------- ---------- ---------- ----------- ----------
         1 A          female            1990         30
         2 B          male              2000         20
         3 C          non-binary        2010         10

View

One downside of virtual columns is that they cannot use functions like SYSDATE, so the year has to be hard-coded. With a view, the expression can reference SYSDATE and will always be up-to-date:

create or replace view child_view as
select id, name, gender, yearOfBirth, extract(year from sysdate) - yearOfBirth yearsOfAge
from child;

Trigger (Warning)

You can also use a trigger to create the value when a row is inserted or updated:

create or replace trigger child_trg
before update or insert on child
for each row
begin
    if updating('YEAROFBIRTH') or inserting then
        :new.yearsOfAge := extract(year from sysdate) - :new.yearOfBirth;
    end if;
end;
/   

But in practice, triggers are a pain to maintain. Which leads to the question: why do you want to store this information in the first place?

Good database design should minimize the amount of redundant data. There are always exceptions, but you should have a good reason for those exceptions, like an especially complicated calculation that you don't want others to get wrong, you can't create a PL/SQL function because of an unusual security constraint, etc. Calculating something as trivial as the age may cause more problems than it solves.