ORA-00036: Recursive SQL level error

Question

I have created a view using 'emp' and 'dept' table.

create or replace view view_test (emp_name,dept_name,location) as
select e.emp_name,d.dept_name,d.location from emp e,dept d
where e.DEPT_NO=d.DEPT_NO;

And an 'INSTEAD OF' trigger:

CREATE OR REPLACE TRIGGER TRIG_TEST
INSTEAD OF UPDATE ON VIEW_TEST
FOR EACH ROW
BEGIN
UPDATE VIEW_TEST
SET LOCATION=:NEW.LOCATION
WHERE DEPT_NAME=OLD.DEPT_NAME;
END;
/

When I am trying to update the value in VIEW_TEST, I am getting the below error:

update view_test
set location ='ASIA'
WHERE DEPT_NAME='HR';

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

Can anyone please help me..

Thanks in Advance!!

Your trigger captures updates on VIEW_TEST then tries to update VIEW_TEST, resulting in an infinite recursion of updates. I suspect you wanted to update DEPT rather than VIEW_TEST? — Ben
– Ben, Commented May 6, 2018 at 10:16
You can't update the view inside the trigger, you have to update the original table, i.e. "DEPT" table. I update posted the answer you can try that, it will solve your issue. — user8406805
– user8406805, Commented May 6, 2018 at 16:27

Kaushik Nayak · Accepted Answer · 2018-05-06 16:15:40Z

An INSTEAD OF TRIGGER is helpful when you know a particular logic for how to handle a DML operation on a VIEW. Generally, if a view contains multiple joins on tables and we would want to ensure how Oracle needs to handle such inserts/updates on the underlying tables of the view, it is recommended to use such a Trigger.

You are committing a big mistake by trying to update the same view inside the Trigger, which, as already told by Ben in the comments, leads to infinite recursion of updates.

I think what you were actually trying to do was to update the dept table.So, you could re-write your Trigger to look something like this.

CREATE OR replace TRIGGER trig_test
  INSTEAD OF UPDATE ON view_test
  FOR EACH ROW
BEGIN
    UPDATE dept
    SET    location  = :NEW.location -- include other columns
    WHERE  dept_name = :OLD.dept_name;
END;
/

You may also add additional updates to other columns of underlying tables. Then, an update statement like yours will work as expected.

update view_test set location ='ASIA' WHERE DEPT_NAME='HR';

user8406805 · Accepted Answer · 2018-05-06 16:25:31Z

0

Below is the correct trigger code, that will solve your problem, try it now.

CREATE OR REPLACE TRIGGER TRIG_TEST
INSTEAD OF UPDATE ON VIEW_TEST
FOR EACH ROW
BEGIN
UPDATE dept
SET LOCATION=:NEW.LOCATION
WHERE DEPT_NO = (select dept_no
                      from dept 
                     where DEPT_NAME=:OLD.DEPT_NAME
                       and rownum <=1);
END;
/

answered May 6, 2018 at 16:25

user8406805

Collectives™ on Stack Overflow

ORA-00036: Recursive SQL level error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related