Forum Stats

  • 3,854,340 Users
  • 2,264,354 Discussions
  • 7,905,651 Comments

Discussions

Instead of Triggers update & insert

2621063
2621063 Member Posts: 36
edited Jul 3, 2014 10:07PM in SQL & PL/SQL

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

Tagged:

Answers

  • In one online tutorial I found the below code

    I see - and you want us to guess what the link to that tutorial actually is?

  • theoa
    theoa Member Posts: 431
    edited Jul 2, 2014 3:23AM

    Two observations:

    I am not familiar with this syntax:

    if updating('ID')

    "updating" is just a boolean pseudo-function which does NOT have a column name as parameter.

    Since the trigger is "instead of update", it will return always true.

    Maybe you mean something like:

    if :new.id != :old.id

    Second, you created statement triggers but it looks to me like they should be row triggers ("instead of ... on ... for each row").

    :new (and :old) won't work in a statement trigger.

  • RogerT
    RogerT Member Posts: 1,859 Gold Trophy

    According to the documentation if UPDATING('ID') is ok.

    My question is:

    As the shown code does not cope with your sql

    create or replace view v_employee

    versus

    update v_emp set last_name = 'aa'

    i wonder whether you have other "typos" like e.g. in your code something like

    else

          update v_emp (????)

          set first_Name = :new.first_Name

         where id = :old.id;

    end if;

    if this was the case, then you'd do a recursive call of the trigger.

    hth

  • theoa
    theoa Member Posts: 431
    Roger wrote:
    
    According to the documentation if UPDATING('ID') is ok.
    

    You're right, I never use it so I forgot.

    The reason I never use it, is that it also fires if ID is updated but unchanged. That happens a lot when using Forms.

  • 2621063
    2621063 Member Posts: 36

    Hi Roger/thoea,

      Here is my updated trigger

    Create or replace trigger v_emp_iu1

    Instead of update

    On v_employee

    For each row

    Declare

       V_error varchar2(256);

    Begin

       If updating('employee_id')

       Then

             V_error:='primary key cannot be updated';

              Raise_application_error(-20999,v_error);

      Else

         Update v_employee

         Set last_name = :new.last_name

         Where employee_Id := old.employee_id;

    End if;

    End;

    Trigger v_emp_iu1 compiled.

    Here is my view data.

    Select * from v_employee

    Employee_id last_name

    600.                Bishop

    500.                Cm

    800.                Deepu

    900.                Divya

    100.                King

    850.                Nav

    I gave the following update statement.

    Update v_employee set last_name = 'aa' where employee_id = 600

    Still getting the same recursive error which I pasted yestwrday. Could you kindly explain me as how to do the recursive call of trigger with my trigger taken as an example

    Thank you

  • user8048037
    user8048037 Member Posts: 25 Blue Ribbon
    edited Jul 3, 2014 2:24AM

    Use to avoid updating the same row again and again::

    IF :new.employee_id != :old.employee_id THEN
      UPDATE v_employee
      SET last_name = :new.last_name
      WHERE employee_id = :old.employee_id;
    END IF;
  • theoa
    theoa Member Posts: 431

    Instead of trying to update v_employee (which cannot be done in this trigger on v_employee itself), you should update table employee.

  • RogerT
    RogerT Member Posts: 1,859 Gold Trophy

    So...an instead of Trigger is a trigger which is executed INSTEAD of the command for which it is defined. So when you define an instead of update trigger on a view, INSTEAD of the update (which would go to the table) the code in the instead of trigger is executed. Now, if you do not update the table in the instead of trigger, there is no way how something will ever change...

    As theoa explained - the problem is, that you update the view again - which will trigger the instead of trigger - which will update the view - which will trigger the instead of trigger - which will update the view .... and so on.

    Roger

  • 2621063
    2621063 Member Posts: 36

    Thank you Roger and Thoea. I changed the update statement from view to table and its now working fine as expected.

This discussion has been closed.