/**
Pre-requisite : Understanding on Triggers
Mutating table error occurs when update action in trigger is processed and the records are fetched by a SELECT statement for validation.
Because the inconsistency is on Select statement values....
**/
create or replace trigger emp_update
after insert or update of sal,ename
on emp
for each row
declare
dummy number;
begin
if :new.ename is not null and ( :new.sal > :old.sal or :old.ename <> :new.ename )
then
begin
select sal into dummy from emp
where ename= :new.ename
and sal > :new.sal;
exception
when no_data_found then
dbms_output.put_line('sdfsdfsdfdsfsdfsdf');
end;
end if;
end;
/
Select *
from emp;
insert into emp values('SUSIL',9000);
commit;
update emp
set sal=9001
where ename='SUSIL';
rollback;
Guys, i shall write the solution for this in the next post.
Happy learning!!!!!
Pre-requisite : Understanding on Triggers
Mutating table error occurs when update action in trigger is processed and the records are fetched by a SELECT statement for validation.
Because the inconsistency is on Select statement values....
**/
create or replace trigger emp_update
after insert or update of sal,ename
on emp
for each row
declare
dummy number;
begin
if :new.ename is not null and ( :new.sal > :old.sal or :old.ename <> :new.ename )
then
begin
select sal into dummy from emp
where ename= :new.ename
and sal > :new.sal;
exception
when no_data_found then
dbms_output.put_line('sdfsdfsdfdsfsdfsdf');
end;
end if;
end;
/
Select *
from emp;
insert into emp values('SUSIL',9000);
commit;
update emp
set sal=9001
where ename='SUSIL';
rollback;
Guys, i shall write the solution for this in the next post.
Happy learning!!!!!
Comments