1

I am trying to create a trigger that on INSERT or UPDATE of a record where the TESTGROUP == 'HSSAG' that a duplicate of that record would be created for the TESTGROUP 'HSBEJ' ..... and when a record for HSSAG was updated, the associated HSBEJ record would then be updated. 

create or replace TRIGGER VAL_TESTTYPE_CLONING 
AFTER DELETE OR INSERT OR UPDATE OF TESTGROUP ON CLONING_TABLE 
FOR EACH ROW 
WHEN (new.TESTGROUP = 'HSSAG') 
BEGIN
  INSERT INTO CLONING_TABLE (TESTCOL, TESTGROUP) VALUES(:new.TESTCOL, 'HSBEJ');
END;

This is the error i get when i try to insert

INSERT INTO "CLMSDBA"."CLONING_TABLE" (TESTCOL, TESTGROUP) VALUES ('3', 'HSSAG')
ORA-04091: table CLMSDBA.CLONING_TABLE is mutating, trigger/function may not see it
ORA-06512: at "CLMSDBA.VAL_TESTTYPE_CLONING", line 2
ORA-04088: error during execution of trigger 'CLMSDBA.VAL_TESTTYPE_CLONING'
ORA-06512: at line 1


One error saving changes to table "CLMSDBA"."CLONING_TABLE":
Row 3: ORA-04091: table CLMSDBA.CLONING_TABLE is mutating, trigger/function may not see it
ORA-06512: at "CLMSDBA.VAL_TESTTYPE_CLONING", line 2
ORA-04088: error during execution of trigger 'CLMSDBA.VAL_TESTTYPE_CLONING'
ORA-06512: at line 1
user3071434
  • 133
  • 1
  • 2
  • 13
  • Does table VAL_TESTTYPE have a column called TESTCOL? (and not for example "TestCol"?) – Tony Andrews Dec 22 '15 at 17:17
  • `:new.TESTCOL` => `:new.TESTGROUP`, assuming the 2 tables have same cols. – DBug Dec 22 '15 at 17:18
  • :new, not new in when – hinotf Dec 22 '15 at 17:23
  • Sorry, I found some mistakes in the table and column name just after I posted that I corrected, then got another error that makes no sense to me. – user3071434 Dec 22 '15 at 17:23
  • you cannot insert into same table inside triiger, if you want substitute some value just use :new.col_name = ... – hinotf Dec 22 '15 at 17:26
  • Are you are saying that I can't use a trigger to INSERT a record into the same table? – user3071434 Dec 22 '15 at 17:30
  • Yes, if you want just replace column value use this: create or replace TRIGGER VAL_TESTTYPE_CLONING before DELETE OR INSERT OR UPDATE OF TESTGROUP ON CLONING_TABLE FOR EACH ROW WHEN (new.TESTGROUP = 'HSSAG') BEGIN :new.testgroup := 'HSBEJ'; END; / – hinotf Dec 22 '15 at 17:34
  • I don't want to replace the column value ...... when a record value for HSSAG is created then a duplicate for HSBEJ needs to be added. I can always create a temp table that triggers back on the sender to accomplish what I need if it can't be done in a single table and trigger. – user3071434 Dec 22 '15 at 17:38
  • May be this hepls http://stackoverflow.com/questions/8167200/insert-trigger-for-inserting-record-in-same-table – hinotf Dec 22 '15 at 17:44
  • I tried using a "temp" table to receive the new values, then have a trigger on the "temp" table insert them into the source table, but that caused a mutation error as well. – user3071434 Dec 29 '15 at 13:10

1 Answers1

0

I found a very good workaround for mutating trigger issues. Hope this helps.

Mutating trigger workaround

Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25