Re: What is mutating table?

From: M T Proctor <martin.proctor_at_dial.pipex.com>
Date: 1997/09/02
Message-ID: <01bcb7e2$53a2c780$LocalHost_at_mproctor>#1/1

A mutating table is one which is currently being modified by a DML statement, in your case the stud_table. You cannot modify the stud_table from an 'each row' trigger stored against it, but you can from a statement trigger. There is a solution to your problem which involves creating an each row and statement trigger and a package. Et Voila....

/* This Package declares a PL/SQL Table int which amended student details are held

   It needs to be a package to ensure table seen globally */ CREATE OR REPLACE PACKAGE StudentData AS

 	TYPE t_stud_id IS TABLE OF stud_table.stud_id%TYPE
	INDEX BY BINARY INTEGER;

 	TYPE t_stud_ag IS TABLE OF stud_table.stud_ag%TYPE
	INDEX BY BINARY INTEGER;
v_stud_id	t_stud_id;
v_stud_ag	t_stud_ag;
v_entry		BINARY_INTEGER := 0;

END StudentData;

/* This row level trigger primes PL/SQL table with details of amended students

    as each row is updated */
CREATE OR REPLACE TRIGGER RStudents
BEFORE INSERT OR UPDATE OF stud_ag ON stud_table FOR EACH ROW
BEGIN

	StudentData.v_entry :- StudentData.v_entry + 1;
	StudentData.v_stud_id(StudentData.v_entry) := :new.stud_id;
	StudentData.v_stud_ag(StudentData.v_entry) := :new.stud_ag;

END RStudents;

/* This statement trigger loops thru PL/SQL table and updates stud_table with

   details */
CREATE OR REPLACE TRIGGER SStudents
AFTER INSERT OR UPDATE OF stud_ag ON stud_table DECLARE

	v_stud_id	stud_table.stud_id%TYPE;
	v_stud_ag	stud_table.stud_ag%TYPE;
BEGIN
	FOR LoopCnt IN 1..StudentData.v_entry LOOP
		v_stud_id	:= StudentData.v_stud_id(LoopCnt);
		v_stud_ag	:= StudentData.v_stud_ag(LoopCnt);

		UPDATE	stud_table
		SET		stud_ag = v_stud_ag
		WHERE	stud_id	= v_stud_id;

	END LOOP;

	-- Reset counter for next execution
	StudentData.v_entry := 0;

END SStudents;

Hope this helps you out me old mucker.

Pro.

Srinivasan Vinod <vks7584_at_tntech.edu> wrote in article <3409A420.34EB_at_tntech.edu>...
> Can anyone tell me what is mutating table and how to solve this problem.
> I came across this error when I wrote a trigger that will fire when
> updating one of the column named stud_ag in table stud_table and it will
> update the column names stud_ssnum in the same table.
>
> PLease reply to vks7584_at_tntech.edu as I seem to lose my posting evertime
> I do ask some question in this newsgroup.
>
> Thanks in advance
>
> vinod
>
Received on Tue Sep 02 1997 - 00:00:00 CEST