Home » SQL & PL/SQL » SQL & PL/SQL » getting error "table table1 is mutating, trigger/function may no" on executing trigger (ORacle 11g)
getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669038] Thu, 29 March 2018 17:57 Go to next message
gold14388
Messages: 4
Registered: March 2018
Junior Member
I am getting an error on executing trigger please help me finding a solution to over come this error


create or replace 
trigger trigger1 AFTER INSERT OR UPDATE ON table1
FOR EACH ROW
DECLARE
  v_Key1 NUMBER;
  v_Key2 NUMBER;
  v_Key3 NUMBER;
  v_Key4 NUMBER;
BEGIN
	IF UPDATING THEN			
          IF NOT UPDATING ('column1') THEN        
             UPDATE table1 SET column1 = SYS_EXTRACT_UTC(SYSTIMESTAMP) WHERE EKey = :NEW.EKey;

          SELECT w.key1, w.key2, u.key3
          INTO v_Key1, v_Key2, v_Key3
          FROM Work w LEFT JOIN "USER" u ON u.UserKey = w.UserKey
          WHERE w.Key = :OLD.EKey;
      END IF;
ELSE

      -- Inserting... just set the last updated date
			UPDATE table1 SET column1 = SYS_EXTRACT_UTC(SYSTIMESTAMP) WHERE EKey = :NEW.EKey;

  END IF;

END;
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669039 is a reply to message #669038] Thu, 29 March 2018 18:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ERROR?
What error?
I don't see any error.

You have trigger that fire upon UPDATE TABLE1; which contains UPDATE TABLE1 that fires trigger that UPDATE TABLE1 which fire TRIGGER that UPDATE TABLE1, etc. etc. etc. ..... (repeat FOREVER)!
Do you see any problem with scenario above?

To avoid MUTATING TABLE error do NOT issue SQL against same table upon which trigger is based.
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669040 is a reply to message #669039] Thu, 29 March 2018 19:06 Go to previous messageGo to next message
gold14388
Messages: 4
Registered: March 2018
Junior Member
@BlackSwan, Thank you so much for your reply. May I know how can I achieve the same goal that I was trying to achieve with the trigger, any recommendations?

[Updated on: Thu, 29 March 2018 19:07]

Report message to a moderator

Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669041 is a reply to message #669040] Thu, 29 March 2018 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Consider sample code below & adjust yours accordingly
CREATE OR replace TRIGGER trg_before_person_update 
  BEFORE UPDATE ON person_records 
  FOR EACH ROW 
DECLARE 
    username VARCHAR2(20); 
BEGIN 
    username := USER; 

    -- Setting updated_by and updated_Date values. 
    :NEW.updated_by := username; 

    :NEW.updated_date := SYSDATE; 
END;
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669042 is a reply to message #669038] Fri, 30 March 2018 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create or replace 
trigger trigger1 AFTER INSERT OR UPDATE ON table1
FOR EACH ROW
DECLARE
  v_Key1 NUMBER;
  v_Key2 NUMBER;
  v_Key3 NUMBER;
  v_Key4 NUMBER;
BEGIN
	IF UPDATING THEN			
          IF NOT UPDATING ('column1') THEN        
             :NEW.column1 = SYS_EXTRACT_UTC(SYSTIMESTAMP);

          SELECT w.key1, w.key2, u.key3
          INTO v_Key1, v_Key2, v_Key3
          FROM Work w LEFT JOIN "USER" u ON u.UserKey = w.UserKey
          WHERE w.Key = :OLD.EKey;
      END IF;
ELSE

      -- Inserting... just set the last updated date
			UPDATE table1 SET column1 = SYS_EXTRACT_UTC(SYSTIMESTAMP) WHERE EKey = :NEW.EKey;

  END IF;

END;
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669043 is a reply to message #669042] Fri, 30 March 2018 01:33 Go to previous messageGo to next message
gold14388
Messages: 4
Registered: March 2018
Junior Member
@Michel Cadot,
I am getting an error if i execute trigger the way you mentioned

Error report:
ORA-04084: cannot change NEW values for this trigger type
04084. 00000 - "cannot change NEW values for this trigger type"
*Cause: New trigger variables can only be changed in before row
insert or update triggers.
*Action: Change the trigger type or remove the variable reference.
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669044 is a reply to message #669043] Fri, 30 March 2018 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Change AFTER to BEFORE.
Of course, after you have updated you can't change the values.

Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669052 is a reply to message #669044] Fri, 30 March 2018 10:51 Go to previous messageGo to next message
gold14388
Messages: 4
Registered: March 2018
Junior Member
@Michel Cadot, Thank you so much.
Re: getting error "table table1 is mutating, trigger/function may no" on executing trigger [message #669053 is a reply to message #669052] Fri, 30 March 2018 10:59 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
why is SELECT in this trigger, because nothing is done with the results?
Previous Topic: identifier 'PACK_GEO.TRAN001' must be declared
Next Topic: Administering queue
Goto Forum:
  


Current Time: Thu Mar 28 05:56:20 CDT 2024