I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item is added to my inventory, I need to auto-assign it the first available physical storage location. When items are consumed, they are removed from the inventory thus freeing up a physical location (i.e. we are recycling these physical locations). I have two tables: one inventory table and one table containing all legal location names/Ids.
Table: ALL_LOCATIONS Location_ID SP.1.1.1.a SP.1.1.1.b SP.1.1.1.c SP.1.1.2.a SP.1.1.2.b SP.1.1.2.c SP.1.1.3.a SP.1.1.3.b SP.1.1.3.c ... SP.25.5.6.c Table: ITEM_INVENTORY Item_ID | Location_ID 1 SP.1.1.1.a 2 SP.1.1.1.b 4 SP.1.1.2.a 5 SP.1.1.2.b 6 SP.1.1.2.c 21 SP.1.1.4.a … …
Note: First available location_ID should be SP.1.1.1.c
I need to create a trigger that will assign the next available Location_ID to the inserted row(s). Searching this site I see several similar questions along these lines, however they are geared towards the logic of determining the next available location. In my case, i think I have that down, but I don't know how to implement it as a trigger. Let's just focus on the insert trigger. The "MINUS" strategy (shown below) works well in picking the next available location, but Oracle doesn't like this inside a trigger since I am reading form the same table that I am editing (throws a mutating table error).
I've done some reading on mutating table errors and some workarounds are suggested (autonomous transactions etc.) however, the key message from my reading is, "you're going about it the wrong way." So my question is, "what's another way of approaching this problem so that I can implement a clean & simple solution without having to hack my way around mutating tables?"
Note: I am certain you can find all manner of things not-quite-right with my trigger code and I will certainly learn something if you point them out -- however my goal here is to learn new ways to approach/think about the fundamental problem with my design.
create or replace TRIGGER Assign_Plate_Location BEFORE INSERT ON ITEM_INVENTORY FOR EACH ROW DECLARE loc VARCHAR(100) := NULL; BEGIN IF(:new.LOCATION_ID IS NULL) THEN BEGIN SELECT LOCATION_ID INTO loc FROM (SELECT DISTINCT LOCATION_ID FROM ALL_LOCATIONS MINUS SELECT DISTINCT LOCATION_ID FROM ITEM_INVENTORY) WHERE ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN loc := NULL; END; IF(loc IS NOT NULL) THEN :new.LOCATION_ID := loc; END IF; END IF; END;