1
votes

I created the trigger to update the oracle data base table after insert.

CREATE OR REPLACE TRIGGER Update_ACU     
  AFTER INSERT ON TBL_ACU      
  FOR EACH ROW    
BEGIN       
  UPDATE TBL_ACU     
  SET    CURRENCY = 'XXX'    
  WHERE  ACCOUNT like '%1568';   
END ;


I inserted record as

insert into TBL_ACU values('23','USD','1231568');

I am getting table ORACLE Mutating trigger error.

Please help me how to resolve this.

2
So after inserting any row, you want all rows that have an account ending with 1568 to have their currency set to 'XXX'? That's a real requirement? - David Aldridge

2 Answers

0
votes

It would be better to use BEFORE INSERT trigger to do this.

Try like this,

CREATE OR REPLACE 
TRIGGER update_acu
     BEFORE INSERT ON tbl_acu
     FOR EACH ROW
     WHEN (NEW.ACCOUNT LIKE '%1568')
BEGIN
     :NEW.currency := 'XXX';
END ;
0
votes

Well, you cannot modify the table from the trigger if the trigger is called upon modification of that table. There are various solutions to this problem including an AFTER STATEMENT trigger and caching modifications in some collection defined in PL/SQL PACKAGE, howewer in your situation I'd rather change the body of your trigger to this:

BEGIN
  IF :NEW.ACCOUNT LIKE '%1568' THEN
      :NEW.CURRENCY := 'XXX';
  END IF;
END;

You can use the :NEW and :OLD variables inside the trigger, which identify the new and old values of the record accordingly. Modifying values of the :NEW record will cause changes in data actually inserted to the database.