Oracle DBMS - Read A Table Before Processing Updating In An AFTER Trigger - Mutating Table

I've been experimenting with Oracle over the past few weeks and I've stumbled upon an issue which I can't seem to wrap my head around. I am building a small property management sys

Solution 1:

Just to clarify, the mutating table exception is thrown because you are trying to read from the rooms table in your function, not because you are trying to read from the properties table. Since you have a row-level trigger on rooms, that means that the rooms table is in the middle of a change when the row-level trigger is firing and that it may be in an inconsistent state. Oracle prevents you from querying the rooms table in that situation because the results are not necessarily deterministic or reproducible.

If you created a statement-level trigger (removing the FOR EACH ROW) and put your logic there, you would no longer encounter a mutating table exception because the rooms table would no longer be in an inconsistent state. A statement-level trigger, though, is not able to see which row(s) were modified. That would mean that you'd need to look across all properties to see which status values should be adjusted. That's not going to be particularly efficient.

At the cost of additional complexity, you can improve the performance by capturing which properties changed in a row-level trigger and then referring to that in a statement-level trigger. That generally requires three triggers and a package, which obviously increases the number of moving pieces substantially (if you're on 11.2, you can use a compound trigger with three component triggers which simplifies things a bit by eliminating the need to use the package). That would look something like

CREATE OR REPLACE PACKAGE trigger_collections
AS
  TYPE modified_property_tbl IS TABLE OF properties.property_id%type;
  g_modified_properties modified_property_tbl;
END;

-- Initialize the collection in a before statement trigger just in case
-- there were values there from a prior run
CREATE OR REPLACE TRIGGER trg_initialize_mod_prop_coll
  BEFORE INSERT OR UPDATE ON rooms
BEGIN
  trigger_collections.g_modified_properties := trigger_collections.modified_property_tbl();
END;

-- Put the property_id of the modified row in the collection
CREATE OR REPLACE TRIGGER trg_populate_mod_prop_coll
  AFTER INSERT OR UPDATE ON rooms
  FOR EACH ROW
BEGIN
  trigger_collections.g_modified_properties.extend();
  trigger_collections.g_modified_properties( trigger_collections.g_modified_properties.count + 1 ) := :new.property_id;
END;

CREATE OR REPLACE TRIGGER trg_process_mod_prop_coll
  AFTER INSERT OR UPDATE ON rooms
BEGIN
  FOR p IN 1 .. trigger_collections.g_modified_properties.count
  LOOP
    IF prop_vacancy_query( trigger_collections.g_modified_properties(i) ) = 0 
    THEN
      ...
END;