Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Table is mutating, trigger/function may not see it?

750696Mar 10 2011 — edited Mar 10 2011
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!
This post has been answered by Anton Scheffer on Mar 10 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 7 2011
Added on Mar 10 2011
4 comments
2,278 views