How to update the table during the runtime

[Gurujothi D]

I have created the following trigger to update the table "T1",

create or replace trigger inserttrigger2
before insert on t1
referencing old as old new as new
for each row
declare
to_date date;
from_date date;
no_of_days number(2);
remaining_days number(2);

begin
to_date := :new.to_date;
from_date := :new.from_date;
no_of_days := (:new.to_date - :new.from_date)+1;
remaining_days := (18 -((:new.to_date - :new.from_date)+1));

insert into t1(to_date,from_date,no_of_days,remaining_days) values 
 (:new.to_date,:new.from_date,:new.no_of_days,:new.remaining_days);

end;

here the from_date and to_date are date columns
and the no_of_days is difference between the from_date and to_date columns,
the remaining date is default 18, and when the user enters the from_date and to_date value
the no_of_days has to be calculated and remaining days should be calculated like
(18- no_of_days) when i create the trigger its created but I cant insert the values,

it showing the following error,
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "HR.INSERTTRIGG ER2", line 13
ORA-04088: error during execution of trigger 'HR.INSERTTRIGG ER2'
ORA-06512: at "HR.INSERTTRIGG ER2", line 13


Whats the error can anyone sugget me the result?

Thank you,
regards,
gurujothi

[Rabbit]

You've created an infinite trigger loop. Your trigger is called before an insert into t1. But inside your trigger, it inserts into t1. So when you insert a row into t1, it calls the trigger, which inserts into t1, which calls the trigger, which inserts into t1, which calls the trigger, which inserts into t1, and so on and so forth. I have no idea why you would want to insert infinite records.

[Gurujothi D]

Hi Rabbit,
Thank you for the reply,actually i dont want to insert infinite times,
i just want to calculate the no_of_days ang remaining_days column from the inserted row i.e from_date and to_date.
Can you please suggest me the steps how can I get it?

Using this trigger or function or something else,please give some tips,please.
Thank you,

regards,
gurujothi

[Rabbit]

I don't know how Oracle does it. You'll have to look in the documentation. But in SQL Server, the triggers have a temp table called inserted.