How to create something to update end date by starting day and duration column automaticly SQL

Question

I want to create something that will update my data in table automaticly but i don't know what to use.

i have nr1 table

create table NR1
(
  id         INTEGER not null,
  price      INTEGER,
  price2     INTEGER,
  start_date DATE,
  end_date   DATE,
  duration   NUMBER
)

and i tried to create trigger which will update always after inserting or updateing my table, end_date, so i tried something like this:

create or replace trigger update_nr1_date
after update or insert on nr1
for each row
 when (new.id>0)
declare
begin
  UPDATE nr1
  set nr1.end_date =add_months(nr1.start_date, nr1.duration);
end;

but it have mutation problems, and i read something about that, and i udersatnd the concept of this. I want to make trigger like this (not sheduler, because i want to get it automaticly after inserting or updating some rows). Is it possible while inserting data to table it itself refill missing columns?

Answer 1

Your trigger needs to before update in order to change the value in the row the trigger is fired again; and it needs to modify the current row's (new) pseudorecord via an assignment statement - you should not be issuing a separate update statement inside the trigger. It will cascade, for a start, and in your example woudl try to update every row in the table. But that is also causing the mutating table error - you're trying to update rows in the table the trigger is against. There are workarounds when that is actually necessary, but it isn't here, that update should just not be there.

So you would do:

create or replace trigger update_nr1_date
before update or insert on nr1
for each row
when (new.id>0)
begin
  :new.end_date := add_months(:new.start_date, :new.duration);
end;
/

But if you're on 11g or higher you can use a virtual column instead, without needing a trigger at all:

create table NR1
(
  id         INTEGER not null,
  price      INTEGER,
  price2     INTEGER,
  start_date DATE,
  end_date   DATE generated always as (add_months(start_date, duration)) virtual,
  duration   NUMBER
)

Then when you insert, skip that column in the statement:

insert into nr1 (id, price, price2, start_date, duration)
values (1, 2, 3, date '2018-06-01', 3);

1 row inserted.

select * from nr1 where id = 1;

        ID      PRICE     PRICE2 START_DATE END_DATE     DURATION
---------- ---------- ---------- ---------- ---------- ----------
         1          2          3 2018-06-01 2018-09-01          3

The end date always reflects the values of the other two columns.