| Trigger-Mutating Error [message #369864] |
Fri, 27 October 2000 18:48  |
Ana
Messages: 23
Registered: October 2000
|
Junior Member |
|
|
Need Help
I have created trigger, which calls stored procedure.
it is giving following error:
mutaing error,trigger/function may not see it.
How to solve this problem.
Thanks,
Ana
|
|
|
|
| Re: Trigger-Mutating Error [message #369866 is a reply to message #369864] |
Mon, 30 October 2000 01:04  |
AndreaB
Messages: 5
Registered: October 2000
|
Junior Member |
|
|
what's then version you are on ?
generally, in a trigger (or in a stored procedure called by a trigger) you cannot access the table where the trigger is defined
|
|
|
|
| Re: Trigger-Mutating Error [message #369868 is a reply to message #369866] |
Mon, 30 October 2000 11:49 |
Ana
Messages: 23
Registered: October 2000
|
Junior Member |
|
|
Hi !
Using oracle 8i. I am using same table for update and stored procedure.
....storing value in :new.d.
.... using select statement keeping values in
variables & passing them to stored produre.
code is something like this
CREATE OR REPLACE TRIGGER mail_trigger
AFTER UPDATE
OF ddate
ON test
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
V_a varchar2(100);
V_b Varchar2(100);
v_c varchar2(100);
V_d varchar2(10);
Begin
v_d := :new.ddate;
If UPDATING THEN
select a,b,c from test where ddate = V_d;
send_mail(a,b,c,d);
end if;
End;
How to solve this problem.
thanks
Ana
|
|
|
|
| Re: Trigger-Mutating Error- example [message #369870 is a reply to message #369868] |
Mon, 30 October 2000 15:49 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Extracted from: http://www.google.com/search?q=cache:www.oreilly.com/catalog/ordevworkbook/chapter/ch16s.html+oracle+design+mutating+trigger+oreilly&hl=en
Also see mutate1.txt, mutate2.txt, mutate3.txt in examples.zip (from ftp://ftp.oreilly.com/pub/examples/oracle/design/)
... At first, you might be tempted to try something like this:
CREATE OR REPLACE TRIGGER employee_t1
BEFORE DELETE
ON employee
FOR EACH ROW
BEGIN
UPDATE employee
SET mgr = null
WHERE mgr = :new.empno;
END;
/
Unfortunately, this trigger results in the mutating trigger error "ORA-04091 table name is mutating, trigger/function may not see it." You can use a combination of packaged variables and different types of triggers to solve this problem.
The first step is to create a package containing an index-by table to hold the ids of the managers who have been deleted:
CREATE OR REPLACE PACKAGE mutating_table_pkg
IS
TYPE array IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_values array;
empty array;
END;
/
The second step is to create a statement-level BEFORE DELETE trigger that fires at the beginning of the transaction; its only purpose is to initialize the emp_values table to make sure it is empty:
CREATE OR REPLACE TRIGGER mutating_trig_1
BEFORE DELETE
ON emp
BEGIN
mutating_table_pkg.emp_values := mutating_table_pkg.empty;
END;
/
The third step is to create a row level BEFORE UPDATE trigger to populate the emp_values tables with the employee numbers of the rows that are being deleted. This is the only type of processing implemented in this trigger; the UPDATE statement is intentionally removed from this trigger because it caused the "mutating table" problem in the first place:
CREATE OR REPLACE TRIGGER mutating_trig_2
BEFORE DELETE
ON emp
FOR EACH ROW
WHEN (old.job = 'MANAGER')
DECLARE
i NUMBER := mutating_table_pkg.emp_values.COUNT + 1;
BEGIN
mutating_table_pkg.emp_values (i).empno := :old.empno;
END;
/
The final step is to create a statement-level AFTER DELETE that uses the array of managers to modify the employee records. At this point, the employee table is no longer a mutating table (undergoing changes), so you're free to make update statements :
CREATE OR REPLACE TRIGGER mut_trg_3
AFTER DELETE
ON emp
BEGIN
FOR i IN 1 .. mutating_table_pkg.emp_values.COUNT
LOOP
UPDATE emp
SET mgr = NULL
WHERE mgr = mutating_table_pkg.emp_values (i).empno;
END LOOP;
END;
/
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
