I am testing a trigger.I have below tables Employee and Sales.

create table employee ( emp_id number(4), mgr_id number(4), duration number(4));

create table sales ( sales_id number(4), responsibleemp_id number(4));

Now my intention is if the Responsible_Employee column value of a new Sales record represents an employee who has been with the company for less than 30 days, the value of Responsible_Employee will instead be set to the manager of that employee. I have written below trigger code.

CREATE OR REPLACE TRIGGER TESTTRIGGER2 BEFORE INSERT ON SALES FOR EACH ROW

DECLARE

V_DURATION NUMBER(4); V_MGRID NUMBER(4); V_EMPID NUMBER(4);

BEGIN V_EMPID := :NEW.responsibleemp_id;

SELECT duration INTO V_DURATION FROM EMPLOYEE WHERE emp_id=V_EMPID ;

IF V_DURATION <=30 THEN

SELECT MGR_ID INTO V_MGRID FROM EMPLOYEE WHERE emp_id=:new.responsibleemp_id ;

INSERT INTO SALES VALUES(:new.SALES_ID,V_MGRID);

ELSE

INSERT INTO SALES VALUES(:new.SALES_ID,:new.responsibleemp_id);

END IF;

END;

But it is giving below error.

INSERT INTO SALES VALUES(500,100) Error report - SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-00036: maximum number of recursive SQL levels (50) exceeded ORA-06512: at "OT.TESTTRIGGER2", line 11 ORA-04088: error during execution of trigger 'OT.TESTTRIGGER2' ORA-06512: at "OT.TESTTRIGGER2", line 26

I have following values in employee.

insert into employee values(100,200,31);

insert into employee values(101,100,20);

insert into employee values(200,0,40);