SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

unknown-7404

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

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

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

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

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

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

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

RogerT

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

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

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 31 2014

Added on Jul 1 2014

#of, #trigger

9 comments

1,325 views

SQL & PL/SQL

Instead of Triggers update & insert

Comments

Post Details