0

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!

  • 1
    Trigger users_after_insert on table USERS makes an insert into table UTILITIES but this insert fires trigger memory_after_insert which selects again table USERS. Solution: put everything in stored procedures instead of using triggers. – Wernfried Domscheit Apr 22 '15 at 20:17
  • I am not as familiar with procedures as I am with triggers. Will I need to change any of my triggers actions/code to properly work as procedures? – kookleton Apr 22 '15 at 20:24
  • You should probably take all of the code and create stored procedures. – Gandolf989 Apr 23 '15 at 13:03
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.