Insert max value from other table automatically by executing a trigger

0
votes

I have created two example tables, test1 and test2.

test1 has three columns: test_id (PK), test_count, test_desc.
test2 has two columns: test_count (PK), test_desc.

From test2 table I want to retrieve the max value of column test_count and I want the value to be inserted into test1 table during each insert into statement execution like below:

insert into test1 (test_id, test_desc) values (1,'blahblahblah');

The value should be inserted automatically by the following trigger execution:

CREATE OR REPLACE TRIGGER maxTestCount
after insert on test1
FOR EACH ROW 
DECLARE
  varMaxCount number(10);
BEGIN 
  Select max(test_count) INTO varMaxCount
  From test2;
  Insert Into test1 (test_count) values (varMaxCount);
END;

It cannot be performed due to the following error:

SQL Error: ORA-04091: table MMDMANAGER.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "MMDMANAGER.MAXTESTCOUNT", line 6
ORA-04088: error during execution of trigger 'MMDMANAGER.MAXTESTCOUNT' 04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.

Would You give me advice how to avoid this error?

2
sqloracleplsql
you cant do inserts into the same table being referenced for the trigger. This is usually an indicator of a design error. Read this - OldProgrammer
This makes no sense. Your design is flawed. Please explain your requirement, and not jump to your design so we could help with "how" to do part of the requirement. Focus on "what" to do part and explain that. - Lalit Kumar B
Seems like you just need to have normalized data in a table, and have a view to have the max(value) data. - Lalit Kumar B

2 Answers

1
votes

You can never attempt to modify a record that is being worked on by trigger, however you can assign values to pseudorecords and trigger will consume those without any issue. But this works when the triggering event is Before and not when after.

CREATE OR REPLACE TRIGGER maxTestCount
before insert on test1
FOR EACH ROW 
DECLARE
  varMaxCount number(10);

BEGIN 

  Select max(test_count) INTO varMaxCount
  From test2;

    :new.test_count := varMaxCount;
END;
/

Refer the complete solution in DBFIDDLE link https://dbfiddle.uk/?rdbms=oracle_18&fiddle=abb1c455a0be4b83ad898a2e334db153

Test Results are below: enter image description here

0
votes

You don't need a select statement to achieve it. You may try below simple trigger -

CREATE OR REPLACE TRIGGER maxTestCount
AFTER INSERT ON test2
FOR EACH ROW
BEGIN  
 Insert Into test1 (test_count) values (:NEW.test_count);
END;