Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    3

    Thumbs up Unanswered: Fun With Triggers and Mutating Tables

    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.
    Code:
    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;
    /
    Any ideas on why it won't pickup the value for v_max_key.
    Is there a work around for this?
    Thanks,
    Kevin
    Last edited by Kevin H; 01-28-10 at 02:04.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With triggers in Oracle the default behaviour is that SQL can not be done against the table upon which the trigger is based.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    3
    Quote Originally Posted by anacedent View Post
    With triggers in Oracle the default behaviour is that SQL can not be done against the table upon which the trigger is based.
    Thats what I've found out. Sub queries against the trigger table are not allowed. What I was looking for is a way around it.

    Any ideas

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What I was looking for is a way around it.
    OK. Do NOT do SQL against base table & error will not occur.

    Doctor, I hurts when I poke myself in the eye. How do I make the pain stop?

    What's the work around for the situation above?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    3
    Quote Originally Posted by anacedent View Post
    >What I was looking for is a way around it.
    OK. Do NOT do SQL against base table & error will not occur.

    Doctor, I hurts when I poke myself in the eye. How do I make the pain stop?

    What's the work around for the situation above?
    Ahhh Hubris how nice.

    I was thinking more along the lines of creating a view of the item table and then query the view from the trigger.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by Kevin H View Post
    Ahhh Hubris how nice.

    I was thinking more along the lines of creating a view of the item table and then query the view from the trigger.
    Let us know the results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I think this is a case where you would be better off with a "staging" table.
    That way you can process the staging table against your "master" table without the
    errors you are hitting.

    1. staging table
    1a. insert into master where > max whatever
    1b. insert into discard table where does not meet 1a criteria
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •