If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > trighelp

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-10-03, 07:49
vadlamanibujji vadlamanibujji is offline
Registered User
 
Join Date: Oct 2002
Posts: 36
trighelp

create or replace TRIGGER tr1 AFTER update ON emp FOR EACH ROW
DECLARE
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.vjob%type;

CURSOR C1 IS
SELECT DISTINCT empno,job,ename
FROM emp
where emp.deptno=:new.deptno;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO vempno,vjob,vename;
EXIT WHEN C1%NOTFOUND;
INSERT INTO dept (lll,mmm)values(vjob,vename)
END LOOP;
CLOSE C1;
END;


the above trigger is giving erro of mutuating how to handle can u plz tellme
Reply With Quote
  #2 (permalink)  
Old 01-10-03, 08:43
andrewst andrewst is offline
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Re: trighelp

Quote:
Originally posted by vadlamanibujji
create or replace TRIGGER tr1 AFTER update ON emp FOR EACH ROW
DECLARE
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.vjob%type;

CURSOR C1 IS
SELECT DISTINCT empno,job,ename
FROM emp
where emp.deptno=:new.deptno;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO vempno,vjob,vename;
EXIT WHEN C1%NOTFOUND;
INSERT INTO dept (lll,mmm)values(vjob,vename)
END LOOP;
CLOSE C1;
END;


the above trigger is giving erro of mutuating how to handle can u plz tellme
Wow, what a strange trigger
Anyway, the usual way to avoid mutating table errors involves creating a package and another trigger.
The idea is to move the SELECT to a statement-level trigger, as these don't suffer from mutating table issues. So the idea is brief is:
1) In FOR EACH ROW trigger, just "remember" necessary details of the triggering row (in this case :NEW.deptno is all you need).
2) In AFTER statement trigger, loop through the "remembered" details and do the SELECTs and other processing.

The package is required to do the remembering. In its simplest form the package would be:

CREATE OR REPLACE PACKAGE tr1_pkg IS
-- Declare a PL/SQL table (array) to remember deptno values
TYPE tab_type IS TABLE OF emp.deptno%TYPE;
tab tab_type;
END;

Your row-level trigger becomes:

create or replace TRIGGER tr1 AFTER update ON emp FOR EACH ROW
BEGIN
-- Remember the deptno
tr1_pkg.tab(trg_pkg.tab.COUNT+1) := :NEW.deptno;
END;

The new AFTER statement-level trigger is:

create or replace TRIGGER tr2 AFTER update ON emp
BEGIN
-- Process each remembered row
FOR i IN 1..tr1_pkg.COUNT
LOOP
INSERT INTO dept (lll,mmm)
SELECT job,ename FROM emp WHERE deptno = tr1_pkg.tab(i);
END LOOP;
-- Clear the PL/SQL table for next time
tr1_pkg.tab.DELETE;
END;

(I have taken the liberty of simplifying your code as well)

I hope this helps. I'm afraid there may be some syntax errors in my code because I haven't tried to compile it.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On