Home » SQL & PL/SQL » SQL & PL/SQL » Fun With Triggers and Mutating tables (10g)
icon14.gif  Fun With Triggers and Mutating tables [message #440913] Wed, 27 January 2010 22:34 Go to next message
Kevin H
Messages: 5
Registered: January 2010
Location: Arizona
Junior Member
Hello everyone. I've been trying to find a way around the Oracle mutating table issue with this trigger.
I need to only insert the records that match the max(item_key).
This is a new business requirement to an existing trigger. I added the code for c2.
Here is the code I have.
CREATE OR REPLACE TRIGGER yada.triggername
after update on yada.ITEM
for each row
Declare
v_ntuser VARCHAR2(30);
v_SQLerrM VARCHAR2(300);
v_plnr VARCHAR2(12);
v_life NUMBER(10);
v_srlfl VARCHAR2(1);
v_srl VARCHAR2(1);
v_lotfl VARCHAR2(1);
v_lot VARCHAR2(1);
v_mkby VARCHAR2(1);
v_make VARCHAR2(1);
v_buy VARCHAR2(1);
v_reqd VARCHAR2(1);
v_insp VARCHAR2(1);
v_plan VARCHAR2(1);
v_max_key VARCHAR2(12);
wrong_item_type EXCEPTION;
cursor c1 IS
SELECT PLANNER_ID, SERIAL_REQD_FL, LOT_REQD_FL,
S_MAKE_BUY_CD, QC_REQD_FL
FROM deltek.PART
WHERE PART_KEY = :new.ITEM_KEY;
cursor c2 IS
SELECT max(ITEM_KEY) FROM yada.ITEM
WHERE ITEM_ID = :new.ITEM_ID;

BEGIN
OPEN c2;
LOOP
FETCH c2 INTO v_max_key;
EXIT WHEN c2%NOTFOUND;
v_ntuser := sys_context('USERENV','OS_USER');

IF v_max_key = :new.ITEM_KEY THEN
IF :new.S_ITEM_TYPE = 'P' THEN
OPEN c1;
LOOP
FETCH c1 INTO v_plnr, v_srlfl, v_lotfl, v_mkby, v_reqd;
EXIT WHEN c1%NOTFOUND;


if v_srlfl = 'Y' then
v_srl := 'T';
else v_srl := 'F';
end if;
if v_lotfl = 'Y' then
v_lot := 'T';
else v_lot := 'F';
end if;
IF v_mkby = 'M' THEN
v_make := 'T';
ELSE v_make := 'F';
END IF;
IF v_mkby = 'B' THEN
v_buy := 'T';
ELSE v_buy := 'F';
END IF;
IF v_reqd = 'Y' THEN
v_insp := 'T';
v_plan := 'T';
ELSE v_insp := 'F';
v_plan := 'F';
END IF;
insert into yada.QA_ITEM_MASTER_STGO values (yada.QA_ITEM_MASTER_SEQ.NEXTVAL,
:new.ITEM_ID, 'MBU', SUBSTR(:new.ITEM_DESC,1,30),SUBSTR(:new.ITEM_DESC,31,60), :new.BUYER_ID,
v_plnr, 0, NULL, :new.ITEM_KEY, :new.PROD_CLASSIF_CD, :new.COMM_CD,
:new.item_rvsn_id, 'U', NULL, NULL, 'A',v_srl,v_lot,'F','F',:new.DFLT_UM_CD,
NULL,v_make,v_buy,v_insp,v_plan,NULL, 'N', SYSDATE, v_ntuser, 0);
END LOOP;
CLOSE c1;
ELSE
RAISE wrong_item_type;
END IF;

ELSE
EXIT;
END IF;

END LOOP;
CLOSE c2;

exception
WHEN wrong_item_type THEN
insert into yada._QA_ERR_AUDIT values
('ITEM', 'UPDATE', SYSDATE, v_ntuser, 'Item: '||:new.ITEM_ID||
' item type is not P and did NOT write into'||
' yada._QA_ITEM_MASTER_STGO.');
WHEN OTHERS THEN
v_SQLerrM := SUBSTR(SQLERRM,1,200);
insert into yada._QA_ERR_AUDIT values
('ITEM', 'UPDATE', SYSDATE, v_ntuser, 'Item: '||:new.ITEM_ID||
' did not write into yada.QA_ITEM_MASTER_STGO. '||:new.ITEM_key||'max'||v_max_key ||v_SQLerrM);
END;
/
Any ideas on why it won't pickup the value for v_max_key.
Is there a work around for this?
Thanks,
Kevin
Re: Fun With Triggers and Mutating tables [message #440916 is a reply to message #440913] Wed, 27 January 2010 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Any ideas on why it won't pickup the value for v_max_key.
I don't understand the question.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

CREATE OR REPLACE TRIGGER yada.triggername
  AFTER UPDATE ON yada.item
  FOR EACH ROW
DECLARE
  v_ntuser   VARCHAR2(30);
  v_sqlerrm  VARCHAR2(300);
  v_plnr     VARCHAR2(12);
  v_life     NUMBER(10);
  v_srlfl    VARCHAR2(1);
  v_srl      VARCHAR2(1);
  v_lotfl    VARCHAR2(1);
  v_lot      VARCHAR2(1);
  v_mkby     VARCHAR2(1);
  v_make     VARCHAR2(1);
  v_buy      VARCHAR2(1);
  v_reqd     VARCHAR2(1);
  v_insp     VARCHAR2(1);
  v_plan     VARCHAR2(1);
  v_max_key  VARCHAR2(12);
  wrong_item_type  EXCEPTION;
  CURSOR c1 IS
    SELECT planner_id,
           serial_reqd_fl,
           lot_reqd_fl,
           s_make_buy_cd,
           qc_reqd_fl
    FROM   deltek.part
    WHERE  part_key = :new.item_key;
  CURSOR c2 IS
    SELECT Max(item_key)
    FROM   yada.item
    WHERE  item_id = :new.item_id;
BEGIN
  OPEN c2;
  
  LOOP
    FETCH c2 INTO v_max_key;
    
    EXIT WHEN c2%NOTFOUND;
    
    v_ntuser := Sys_context('USERENV','OS_USER');
    
    IF v_max_key = :new.item_key THEN
      IF :new.s_item_type = 'P' THEN
        OPEN c1;
        
        LOOP
          FETCH c1 INTO v_plnr,v_srlfl,v_lotfl,v_mkby,
          v_reqd;
          
          EXIT WHEN c1%NOTFOUND;
          
          IF v_srlfl = 'Y' THEN
            v_srl := 'T';
          ELSE
            v_srl := 'F';
          END IF;
          
          IF v_lotfl = 'Y' THEN
            v_lot := 'T';
          ELSE
            v_lot := 'F';
          END IF;
          
          IF v_mkby = 'M' THEN
            v_make := 'T';
          ELSE
            v_make := 'F';
          END IF;
          
          IF v_mkby = 'B' THEN
            v_buy := 'T';
          ELSE
            v_buy := 'F';
          END IF;
          
          IF v_reqd = 'Y' THEN
            v_insp := 'T';
            
            v_plan := 'T';
          ELSE
            v_insp := 'F';
            
            v_plan := 'F';
          END IF;
          
          INSERT INTO yada.qa_item_master_stgo
          VALUES     (yada.qa_item_master_seq.nextval,
                      :new.item_id,
                      'MBU',
                      Substr(:new.item_desc,1,30),
                      Substr(:new.item_desc,31,60),
                      :new.buyer_id,
                      v_plnr,
                      0,
                      NULL,
                      :new.item_key,
                      :new.prod_classif_cd,
                      :new.comm_cd,
                      :new.item_rvsn_id,
                      'U',
                      NULL,
                      NULL,
                      'A',
                      v_srl,
                      v_lot,
                      'F',
                      'F',
                      :new.dflt_um_cd,
                      NULL,
                      v_make,
                      v_buy,
                      v_insp,
                      v_plan,
                      NULL,
                      'N',
                      SYSDATE,
                      v_ntuser,
                      0);
        END LOOP;
        
        CLOSE c1;
      ELSE
        RAISE wrong_item_type;
      END IF;
    ELSE
      EXIT;
    END IF;
  END LOOP;
  
  CLOSE c2;
EXCEPTION
  WHEN wrong_item_type THEN
    INSERT INTO yada._qa_err_audit
    VALUES     ('ITEM',
                'UPDATE',
                SYSDATE,
                v_ntuser,
                'Item: '
                ||:new.item_id
                ||' item type is not P and did NOT write into'
                ||' yada._QA_ITEM_MASTER_STGO.');
  WHEN OTHERS THEN
    v_sqlerrm := Substr(sqlerrm,1,200);
    
    INSERT INTO yada._qa_err_audit
    VALUES     ('ITEM',
                'UPDATE',
                SYSDATE,
                v_ntuser,
                'Item: '
                ||:new.item_id
                ||' did not write into yada.QA_ITEM_MASTER_STGO. '
                ||:new.item_key
                ||'max'
                ||v_max_key
                ||v_sqlerrm);
END;
/ 
Re: Fun With Triggers and Mutating tables [message #440921 is a reply to message #440916] Wed, 27 January 2010 23:14 Go to previous messageGo to next message
Kevin H
Messages: 5
Registered: January 2010
Location: Arizona
Junior Member
Sorry about the formatting.
I've cleaned up a bit of the code that not needed to get my point across.

DECLARE
   v_plnr     VARCHAR2(12);
   v_srlfl    VARCHAR2(1);
   v_lotfl    VARCHAR2(1);
   v_mkby     VARCHAR2(1);
   v_reqd     VARCHAR2(1);
   v_max_key  VARCHAR2(12);
   wrong_item_type  EXCEPTION;
   CURSOR c1 IS
      SELECT planner_id,
             serial_reqd_fl,
             lot_reqd_fl,
             s_make_buy_cd,
             qc_reqd_fl
      FROM   deltek.part
      WHERE  part_key = :new.item_key;
    CURSOR c2 IS
      SELECT Max(item_key)
      FROM   yada.item
      WHERE  item_id = :new.item_id;
  BEGIN
    OPEN c2;
  
    LOOP
      FETCH c2 INTO v_max_key;
    
      EXIT WHEN c2%NOTFOUND;  
 
      IF v_max_key = :new.item_key THEN
        IF :new.s_item_type = 'P' THEN
          OPEN c1;
        
          LOOP
            FETCH c1 INTO v_plnr,v_srlfl,v_lotfl,v_mkby,
            v_reqd;
          
            EXIT WHEN c1%NOTFOUND;
                             
            INSERT INTO yada.qa_item_master_stgo
            VALUES     (:new.item_id,
                        :new.item_key,
		        v_plnr,
                        v_srlfl,
                        v_lotfl,
                        v_mkby,
                        v_reqd);
          END LOOP;
        
          CLOSE c1;
        ELSE
          RAISE wrong_item_type;
        END IF;
      ELSE
        EXIT;
      END IF;
    END LOOP;
   
    CLOSE c2;
END;
/ 

I basicly need to have the find some way to have the trigger update the table yada.qa_item_master_stgo with only data that has the most recent item_key.

The item table keeps history on the item_id and each time a new version of the item_id is created a new record with the same item_id and a new item_key is created.

The select statement in c2 is where there is an issue. In my error handleing it give me a "ORA-04091: table yada.ITEM is mutating, trigger/function may not see it"

How would I be able to only update with the most recent version of the ITEM_ID in this trigger.

FYI: This trigger is existing and I added the cursor c2 trying to solve the porblem.

Thanks

Code formatting added by BlackSwan

[Updated on: Wed, 27 January 2010 23:19] by Moderator

Report message to a moderator

Re: Fun With Triggers and Mutating tables [message #440924 is a reply to message #440913] Wed, 27 January 2010 23:18 Go to previous messageGo to next message
Kevin H
Messages: 5
Registered: January 2010
Location: Arizona
Junior Member
Ok I've tried three times to format it and it keeps left justifying. Mad
Re: Fun With Triggers and Mutating tables [message #440925 is a reply to message #440924] Wed, 27 January 2010 23:21 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Kevin H wrote on Wed, 27 January 2010 21:18
Ok I've tried three times to format it and it keeps left justifying. Mad


how to FORMAT is explained, described & shown in http://www.orafaq.com/forum/t/88153/0/
Re: Fun With Triggers and Mutating tables [message #440927 is a reply to message #440921] Wed, 27 January 2010 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>The select statement in c2 is where there is an issue.
Do not do SQL against table upon which trigger is based.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9579487119866

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:290416059674

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:469621337269

Re: Fun With Triggers and Mutating tables [message #440932 is a reply to message #440927] Wed, 27 January 2010 23:56 Go to previous messageGo to next message
Kevin H
Messages: 5
Registered: January 2010
Location: Arizona
Junior Member
Thanks Blackswan.

Now that I know theres an issue with a subquery againt the trigger table. What do you think about building a view and then query the view in the trigger.

[Updated on: Wed, 27 January 2010 23:58]

Report message to a moderator

Re: Fun With Triggers and Mutating tables [message #440935 is a reply to message #440932] Thu, 28 January 2010 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>What do you think sbout building a view and then query the view in the trigger.

I have never tried.
Does VIEW obtain :new or :old values?
How does VIEW behave in multi-user environment?
Which user's data does VIEW return & is it :old or :new?

If you accept the risk of getting wrong value, then be my guest!

I suggest you have a basic DESIGN flaw!
Re: Fun With Triggers and Mutating tables [message #440982 is a reply to message #440913] Thu, 28 January 2010 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
A normal view won't work since it's no different to selecting against the table.
A materilized view might work, but I kind of doubt it. It'd have to be a fast refresh view and even then it wouldn't reflect any changes to the table caused by the operation that fired the trigger, because it wouldn't be updated with those changes untill after the trigger had finished.

There is a way round this using after statement triggers - you can find examples on asktom - but it's complicated and can easily cause problems. You'd be better off writing a procedure to update the item table and putting your trigger code in that.

Some other notes on your code:
1) the loop is pointless - c2 can only ever return 1 row.
2) variables should be typed to columns, that way if you change the size of the table column you don't need to change your code.
3) If you're going to log errors in your exception handlers then you should write a seperate procedure that does an autonomous_transaction to do the insert and call that from the exception handler. And you should re-raise the error afterwards otherwise you aren't going to notice for ages if there is a bug.
Re: Fun With Triggers and Mutating tables [message #441216 is a reply to message #440913] Fri, 29 January 2010 08:56 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
you can use instead of triggers to do this.

But you need to think about locking in a multiuser environment. There is a reason oracle is preventing you from doing this operation inside a table trigger.

Good luck, Kevin
Previous Topic: Cursor
Next Topic: Multiple Cursor with the same name
Goto Forum:
  


Current Time: Thu Sep 29 00:32:56 CDT 2016

Total time taken to generate the page: 0.14958 seconds