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?