SQL Using Multiple Triggers With Each Other

Question

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 below:

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!

Are you the same person that posted basically the same question over on DBA http://dba.stackexchange.com/questions/98592/oracle-sql-complications-with-multiple-triggers Or are you both just working on the same homework assignment? Is there a reason that you are using a trigger here rather than using a stored procedure? — Justin Cave, Apr 22 '15 at 22:30
It was indeed one of my lab partners who posted that. And to answer why we are using triggers rather than procedures well that is simply the way it was setup. So how might the procedure look if you converted the triggers into procedures? I haven't used procedures hardly at all. — Blued00d, Apr 22 '15 at 22:55
possible duplicate of [ora-04091 table is Mutating-](http://stackoverflow.com/questions/19150671/ora-04091-table-is-mutating) and [ORA-04091: table name is mutating](http://stackoverflow.com/questions/14441167/ora-04091-table-name-is-mutating) and [How to avoid ORA-04091 error within a trigger](http://stackoverflow.com/questions/10144224/how-to-avoid-ora-04091-error-within-a-trigger) and... C'mon - take a look up at the top of the page on the right side. See the box with the word "Search" in it? Type "ORA-04091" in it, press return, and you'll get a bunch of stuff to look at. Bon appetit! — Bob Jarvis - Слава Україні, Apr 22 '15 at 23:26

score 0 · Answer 1 · answered Apr 23 '15 at 03:29

To begin with, there is an issue with your first two queries. There is no where condition when you update the USERS table with the memory usage. You should add:

WHERE USERS.User_id = :new.User_id;

otherwise, the entire table gets locked even if you want to update only a single users record.

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) WHERE USERS.User_id = :new.User_id;
END;

I think the reason you are getting error (users_after_insert) is because your first trigger reciprocally triggers when you insert the new default utilities for the user, and in turn locks the entire USERS table. So doing this change should fix your issue.

score 0 · Answer 2 · answered Apr 23 '15 at 04:16

I have created a procedure to use the 2 update statements. You can see if it suits your needs, the trigger is created and inserting values into the utilities table.

SQL> CREATE OR REPLACE TRIGGER users_after_insert
  2    AFTER INSERT ON USERS
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO UTILITIES (UTILITY_NAME, RUNNING, USER_ID, UTILITY_ID, COST)
  6    VALUES
  7    ('Javaw.exe', 'YES', :new.USER_ID,seq_utility_id.nextval, round(dbms_random.value(25000, 100000)));
  8
  9  END;
 10  /

Trigger created.



SQL> create or replace procedure sum_Data
2  is
3  begin
4   UPDATE USERS
5    SET Storage_Size  = (SELECT SUM(FILE_SIZE) FROM FILES WHERE USERS.USER_ID = FILES.USER_ID);
6  UPDATE USERS
7     SET MEMORY_USAGE = (SELECT SUM(COST) FROM UTILITIES WHERE USERS.USER_ID = UTILITIES.USER_ID);
8  END;
9  /

procedure created.




SQL> insert into users (user_id,user_name,user_pass) values (124,'ALPHA','xxxxxx');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from users;

   USER_ID USER_NAME          USER_PASS          STORAGE_SIZE       MEMORY_USAGE
---------- ------------------ ------------------ ------------------ ------------
       124 ALPHA              xxxxxx

SQL> select * from utilities;

UTILITY_ID UTILITY_NAME             COST RUNN    USER_ID
---------- ------------------ ---------- ---- ----------
        10 Javaw.exe               49780 YES         124

SQL>

SQL Using Multiple Triggers With Each Other

2 Answers2