I am writing a simple trigger that is supposed to just send a message with the updated Count of rows as well as the old value of Gender and the updated value of Gender. When i run an update however I am getting the error that the table is mutating and the table might not be able to see it but I'm not exactly sure why.
trigger
create or replace trigger updatePERSONS
after update
on PERSONS
for each row
declare
n int;
oldGender varchar(20):= :OLD.Gender;
newGender varchar(20):= :NEW.Gender;
begin
select Count(*)
into n
from PERSONS;
if (oldGender != newGender) then
dbms_output.put_line('There are now '|| n || ' rows after update. Old gender: ' || oldGender
|| ', new Gender: ' || newGender);
end if;
End;
`
i know it has to do with the select statement after begin but how else would i get count of rows?
select count(*)statement, in a row level trigger, you cannot refer the table on which you have created the trigger. Here you have created the trigger on PERSONS table and you are trying to fetch data from the same table inside the trigger, which is not allowed in Oracle. - San