Oracle SQL Complications With Multiple Triggers

oracletrigger

Hey all so I am creating a DB that is used going to be used for managing utilities of a system. My DB has 3 tables: Users, Utilites, and Files (leaving Files table out because question shouldn't affect it).

TABLES

CREATE TABLE USERS (
User_id Number(38,0) NOT NULL PRIMARY KEY,
User_name char(18) NULL ,
User_pass varchar(18) NULL ,
Storage_Size varchar(18) NULL ,
Memory_Usage Number(38,0) NULL
);

CREATE TABLE UTILITIES (
Utility_id Number(38,0) NOT NULL PRIMARY KEY,
Utility_Name varchar(18) NULL ,
Cost Number(38,0) NULL ,
Running char(4) NULL ,
User_id Number(38,0) NULL ,
);

TRIGGERS

//Trigger to SUM the cost of all UTILITIES:

CREATE OR REPLACE TRIGGER memory_after_insert
AFTER INSERT OR UPDATE OR DELETE
ON UTILITIES
BEGIN
UPDATE USERS
SET MEMORY_USAGE = (SELECT SUM(COST) FROM UTILITIES WHERE USERS.USER_ID = UTILITIES.USER_ID);
END;

//Trigger to SUM the cost of all FILES

CREATE OR REPLACE TRIGGER storage_after_insert
AFTER INSERT OR UPDATE OR DELETE
ON FILES
BEGIN
UPDATE USERS
SET STORAGE_USAGE = (SELECT SUM(FILE_SIZE) FROM FILES WHERE USERS.USER_ID = FILES.USER_ID);
END;

Now I would like to create a Trigger that on INSERT into USERS (User creation) will INSERT into UTILITIES all of the systems Utilities (I have X default utilities I would like every user to have)

Example Trigger I have come up with:

CREATE OR REPLACE TRIGGER users_after_insert
  AFTER INSERT ON USERS
  FOR EACH ROW
BEGIN
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Javaw.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(25000, 100000)));
  INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  VALUES
  ('Firefox.exe', 'YES', :new.USER_ID, seq_utility_id.nextval, round(dbms_random.value(60000, 200000)));
END;

When I have this trigger in my DB it causes my other triggers to mess up. I get errors: ORA-04091: table STUDENT052.USERS is mutating, trigger/function may not see it
ORA-06512: at "STUDENT052.MEMORY_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.MEMORY_AFTER_INSERT'
ORA-06512: at "STUDENT052.USERS_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'STUDENT052.USERS_AFTER_INSERT'

Before adding this third trigger, the other 2 triggers were working perfectly. I assume the problem lies within this trigger. If anyone has any knowledge on how to fix this it would be greatly appreciated! Thanks!

Best Answer

I am generally opposed to triggers. They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later.

Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for? Then you don't need triggers at all.