Skip to Main Content

Use Global Temp Table to overcome Mutating Trigger issue

M1k3GFeb 23 2012 — edited Feb 23 2012
Hello all. I need to delete a line in a table, and thought I would get around the mutating trigger issue by creating a GTB table. I created 2 different triggers. One is a Row level trigger, the other a Statement level trigger.
The first trigger gathers the information I need to identify the line I want to delete. This is:
CREATE OR REPLACE TRIGGER Requisition_Capture
AFTER UPDATE OF Delivery_Code on Supply_Items
FOR EACH ROW
BEGIN

  IF :NEW.Delivery_Code = '#' THEN
    INSERT INTO Requisition_Storage
      (Req_Code)
    VALUES
      (:NEW.Requisition_Code);
  END IF;
END;
And the second trigger deletes the line:
CREATE OR REPLACE TRIGGER SUPPLY_ITEM_RESET
AFTER INSERT ON Requisition_Storage

DECLARE

BEGIN

  DELETE FROM Supply_Items r
   WHERE r.Requisition_Code =
         (SELECT t.Req_Code
            FROM Requisition_Storage t, Supply_Items s
           WHERE t.Req_Code = s.Requisition_Code)
     AND r.Order_Qty = 0;

END;
The GTB is as follows stores the information I need to delete the line.:
-- Create table
create global temporary table REQUISITION_STORAGE
(
  req_code VARCHAR2(20)
)
on commit delete rows;
When the column Delivery_Code is updated in the Supply_Item table, and the value is reset to '#', I want to capture the Requisition_Code in the GTB, so I can run the statement level trigger and delete the reset row. However, I still have a mutating error problem. What am I missing?
Comments
Post Details
Added on Feb 23 2012
4 comments
1,027 views