0
votes

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?

1
The problem is there with the 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
ok, i figured as much. where should i put it instead? or how else can i get the count? - user3587186

1 Answers

2
votes

As @San points out, a row-level trigger on persons cannot generally query the persons table.

You'd need two triggers, a row-level trigger that can see the old and new gender and a statement-level trigger that can do the count. You could also, if you're using 11g, create a compound trigger with both row- and statement-level blocks.

create or replace trigger trg_stmt
  after update
  on persons
declare
  l_cnt integer;
begin
  select count(*)
    into l_cnt
    from persons;

  dbms_output.put_line( 'There are now ' || l_cnt || ' rows.' );
end;

create or replace trigger trg_row
  after update
  on persons
  for each row
begin
  if( :new.gender != :old.gender )
  then
    dbms_output.put_line( 'Old gender = ' || :old.gender || ', new gender = ' || :new.gender );
  end if;
end;