2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Setting a column on a new row with a trigger

I have a table name dblog where the schema is like

data_balance_id number(8) primary key,

plan_id number(6) not null,

start_date date default current_date,

end_date date not null);

So I am trying to create a trigger which will update enddate column when a insertion is going to happen. enddate will be updated as 30 days from the insertion day. My trigger code is

CREATE OR REPLACE TRIGGER trg

BEFORE INSERT

ON dblog FOR EACH ROW

 BEGIN

  INSERT INTO dblog (end_date) values (SYSDATE()+30);

END;

/

The insert query is like following

insert into dblog (db_id, planid) values (12,123);

Trigger is created without any error. But at the time of insertion I am getting the following error

insert into dblog (db_id, planid) values (12,123)

ERROR at line 1:

ORA-00036: maximum number of recursive SQL levels (50) exceeded

ORA-06512: at "E1038351.TRG1", line 2

userimage

You just want to modify the :new pseudo-record. Something like this

CREATE OR REPLACE TRIGGER trg

  BEFORE INSERT ON dblog

  FOR EACH ROW

BEGIN

  :new.end_date := sysdate + 30;

END;

If you don't want end_date to have a time component (or, rather, you want the time component to be midnight), you would want to trunc(sysdate) + 30.

Answer is