Link to home
Start Free TrialLog in
Avatar of subwrc
subwrc

asked on

trigger (mutating table error)


I use the following trigger to check that the sum of percentage of properties of a vehicle can't be more than 100%.


create or replace trigger check_percentage
before insert or update of PER_PROP on PROPERTY
for each row
declare
sum PROPERTY.PER_PROP%TYPE;

begin

if inserting then
SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
if((sum + :new.PER_PROP )>100) then
raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given' || to_char(100 - sum));
end if ;
end if ;

if updating then
SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
if((sum + :new.PER_PROP - ld.PER_PROP )>100) then
raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given'|| to_char(100 - sum - ld.PER_PROP));
end if ;
end if;

end;
/


ERROR at line 1:
ORA-04091: table PSS12.PROPERTY is mutating, trigger/function may not see it
ORA-06512: at "PSS12.CHECK_PERCENTAGE", line 14
ORA-04088: error during execution of trigger 'PSS12.CHECK_PERCENTAGE'



I need something to avoid this error.

ASKER CERTIFIED SOLUTION
Avatar of andrewst
andrewst

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial