I get an error like that:
ORA-04091: table TRACKER.REQUESTS is mutating, trigger/function may not see it
ORA-06512: at "TRACKER.REQUESTS_EMAIL", line 4
ORA-06512: at "TRACKER.REQUESTS_EMAIL", line 16
ORA-04088: error during execution of trigger
My trigger code is:
create or replace trigger "REQUESTS_EMAIL"
after update of status
on requests
referencing new as new old as old
for each row
declare
cursor mail_overdue
is
select username, assigned_email, form_name, request_id
from assigned, form_type, requests
where assigned.assigned_id = form_type.assigned_id
and form_type.form_id = requests.related_form_id(+)
and :new.status = 'Overdue';
request_id number;
v_request_id number;
v_mail varchar2 (250);
v_user varchar2 (250);
v_form varchar2 (250);
begin
for emp_rec in mail_overdue
loop
v_request_id := emp_rec.request_id;
v_mail := emp_rec.assigned_email;
v_user := emp_rec.username;
v_form := emp_rec.form_name;
apex_mail.send (
p_to => v_mail,
p_from => v_mail,
p_body => 'You have an overdue request in Request Tracker. '
|| CHR (10)
|| ' Request: '
|| v_request_id
|| CHR (10)
|| ' Type: '
|| v_form
|| CHR (10)
|| ' Login: '
|| v_user
|| CHR (10)
|| ' Pass: '
|| '******',
p_subj => 'Overdue Request'
);
end loop;
end;
/
The trigger compiles with no errors. The error I get when trying to update the table!
I suspect a problem is in the trigger's code?
I absolutely need to make a trigger as "after" update.
Thank you for consideration!