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;
/