Trigger to find next available inventory location

Question

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;

Answer 1

There are several ways to do it. You could add column AVAILABLE or OCCUPIED to first table and select data only from this table with where available = 'Y'. In this case you need also triggers for delete and for update of location_id on second table.

Second option - when inserting data use merge or some procedure retrieving data from all_locations when item_inventory.location_id is null.

Third option - Oracle 11g introduced compound triggers which allows better handling mutating tables. In this case trigger would look something like this:

create or replace trigger assign_plate_location
for insert on item_inventory compound trigger

  loc varchar2(15) := null;
  type t_locs is table of item_inventory.location_id%type;
  v_locs t_locs;
  i number := 1;

  before statement is 
  begin
    select location_id 
      bulk collect into v_locs from all_locations al
      where not exists (
        select location_id from item_inventory ii 
          where ii.location_id = al.location_id );
  end before statement;

  before each row is
  begin
    if :new.location_id is null then
      if i <= v_locs.count() then 
        :new.location_id := v_locs(i);
        i := i + 1;
      end if;
    end if;
  end before each row;
end assign_plate_location;

I tested it on data from your example, inserts (with select) looked OK. You can give it a try, check if it's efficient, maybe this will suit you.

And last notes - in your select you do not need distinct, MINUS makes values distinct. Also think about ordering data, now your select (and mine) may take random row from ALL_LOCATIONS.