Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Instead of Triggers update & insert

2621063Jul 1 2014 — edited Jul 3 2014

Hi All,


I am learning about the usage of instead of triggers. In one online tutorial I found the below code & I replicated the same from my side. Given below is the table


select * from Employee


ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION

---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------

01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer

02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester

03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester

04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager

05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester

06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester

07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager

08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester


The view


create or replace view v_employee as

select id, first_Name

from employee

order by first_Name;


Trigger


create or replace trigger v_emp_iu

INSTEAD OF UPDATE

on v_employee

declare

  v_error VARCHAR2(256);

begin

  if updating('ID')

  then

       v_error:='You cannot update the PK!';

       raise_application_error (-20999,v_error);

else

      update employee

      set first_Name = :new.first_Name

     where id = :old.id;

end if;

end;


update v_employee set first_name='aa';

update v_employee set id = 0;


when I gave the first update statement I am getting the following errors.

Error starting at line 1 in command:

update v_emp set last_name = 'aa'

Error report:

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

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 8

00036. 00000 -  "maximum number of recursive SQL levels (%s) exceeded"

*Cause:    An attempt was made to go more than the specified number

           of recursive SQL levels.

*Action:   Remove the recursive SQL, possibly a recursive trigger.

Error starting at line 1 in command:

update v_emp set last_name = 'aa'

Error report:

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

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

ORA-04088: error during execution of trigger 'SYSTEM.V_EMP_IU'

ORA-06512: at "SYSTEM.V_EMP_IU", line 3

00036. 00000 -  "maximum number of recursive SQL levels (%s) exceeded"

*Cause:    An attempt was made to go more than the specified number

           of recursive SQL levels.

*Action:   Remove the recursive SQL, possibly a recursive trigger.

Kindly let me know as how to update the view.

INSERT TRIGGER

create or replace trigger insert_emp_dept

  instead of insert on emp_dept_join

declare

  v_department_id departments.department_id%type;

begin

  select department_id into v_department_id

  from departments

  where department_id = :new.department_id;

 

  EXCEPTION

    when no_data_found then

      insert into departments(department_id, dept_name)

      values(dept_sequence.nextval,:new.department_name);

  -- returning id into v_department_id;

insert into employees(employee_id,last_name, department_id)

values(emp_sequence.nextval,:new.last_name,v_department_id);

end;

For the trigger above I am getting the compilation error Error(15,36): PLS-00049: bad bind variable 'NEW.DEPARTMENT_NAME'.

Pls let me know your ideas on this.

Thanks in Advance

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2014
Added on Jul 1 2014
9 comments
648 views