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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments