Cannot update parent rows with trigger on the same table

Question

I currently have the following two tables in my database

CREATE TABLE folders (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  parentId NUMBER,
  CONSTRAINT pk_folders PRIMARY KEY (id),
  CONSTRAINT fk_folders_parent FOREIGN KEY(parentId) REFERENCES folders(id)
);

CREATE TABLE files (
  id NUMBER NOT NULL,
  name VARCHAR2(100) NOT NULL,
  bytes NUMBER DEFAULT 0 NOT NULL,
  folderId NUMBER NOT NULL,
  CONSTRAINT fk_folders FOREIGN KEY (folderId) REFERENCES folders(id)
);

There are also row triggers on the files table to update the bytes column of the folder when a row in files is inserted, deleted, or updated. This is in order to keep track of the total size of the folder. The triggers work fine in this case and successfully update the folder with the total bytes of all files contained in that folder.

CREATE OR REPLACE TRIGGER ari_files
AFTER INSERT ON files
FOR EACH ROW
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + :NEW.bytes
  WHERE f.id = :NEW.folderid;
END ari_files;

I would like to also have the folders update their parent as well so that each folder would keep the sum of its files + any files contained in subfolders. The problem I am running into is when I try to add a trigger on the folders table to update the bytes value for its parentId. I'm getting a mutating table error (ORA-04091) because the triggers on the folders table are also updating the folders table (although it is always going to be a different row and there won't be any loops in the hierarchy)

CREATE OR REPLACE TRIGGER aru_bytes_folders
AFTER UPDATE OF bytes ON folders
FOR EACH ROW
WHEN (OLD.parentId IS NOT NULL)
BEGIN
  UPDATE folders f
  SET f.bytes = f.bytes + (:NEW.bytes - :OLD.bytes)
  WHERE f.id = :OLD.parentId;
END aru_bytes_folders;

Is there something that can be done to get this to work?

The closest that I've gotten so far is creating a temporary table and having row triggers populate the table and then having a statement trigger to read from the temporary table and process the update on its parent (which would trigger more updates). The problem that I ran into with that was that the temporary table was containing previous updates that had been processed already and the database was getting stuck in a loop.

Answer 1

It looks like what you want to do is actually have a summary of the sum of used bytes in each folder, right? I believe that triggers in Oracle are not the best choice of technology to provide you this functionality. This could be better modeled using a view, or even better, a materialized view.

The problem with triggers is that they will serialize the DML to the table and will not scale too well. Imagine a situation where you are updating and inserting lots of rows... for each one of them you'll have an update to the folder table.

If you model your problem with a view instead you would not incur the overhead of the extra DML on the folder table. A simple query like this should do the trick:

SELECT fd.name AS folder_name,
       SUM(f.bytes) AS folder_size
  FROM folders fd, files f
 WHERE fd.id = f.folderId
 GROUP BY fd.name;

You may then make this query persistent by creating a view (the sum will run every time you query the view) or a materialized view with fast refresh on commit (in this case the materialized view stores the calculation as a pre-computed table, useful if the table is huge and the sum is costly).

The materialized view "fast refresh on commit" feature delegates to Oracle the task of recalculating the agregation every time the underlying table(s) changes.

The Data Warehousing Guide has an in dept explanation on this feature: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG015

Answer 2

The mutating tables error is there to prevent inconsistencies in the database from happening. There are some hacks around it, but they are almost always a bad idea.

Tom Kyte has published a good review on why triggers are generally a bad idea. The issue with mutating tables is discussed in the section marked "Incorrect Implementations".

In this case, the solution with a view proposed by Daniela is probably the best workable.