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(-2
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(-2
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.