Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Trigger
Mutating Trigger [message #316246] Thu, 24 April 2008 06:51 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

The following trigger is throwing error as the table is mutating.

CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iLastUpdatedSourceCount NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO iLastUpdatedSourceCount
  FROM 
    POB_USR PU,
  	POB_CHANNEL PC
  WHERE
  	PU.POB_USR_ID = :NEW.LAST_UPDATED_SOURCE AND
  	PU.POB_CHANNEL_ID = PC.POB_CHANNEL_ID AND
  	PC.CHANNEL_ID IN (1,5);
  IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS
  OR NVL(:NEW.POB_VENDOR_SHIP_ADDRESS_2,'0') <> NVL(:OLD.POB_VENDOR_SHIP_ADDRESS_2,'0')
  OR :NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID
  OR NVL(:NEW.POB_REGION,'0') <> NVL(:OLD.POB_REGION,'0')
  OR :NEW.POB_CITY <> :OLD.POB_CITY
  OR :NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL
  OR NVL(:NEW.POB_VENDOR_PAYS_FREIGHT,2) <> :OLD.POB_VENDOR_PAYS_FREIGHT
  OR :NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM
  OR :NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND (:OLD.POB_VENDOR_CLAIM_STATUS <> 10)
  AND :NEW.INACTIVE = 0
  AND iLastUpdatedSourceCount = 1
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  :NEW.POB_EMAIL_SENT := 0;
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = (SELECT 
                                 POB_VENDOR_CLAIM_ID 
                               FROM 
                                 POB_VENDOR_CLAIM 
                               WHERE 
                                 POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID AND
                                 INACTIVE = 0);
  END IF;
END;


I understand that the mutating is happening because of the select I have made from the table that I am updating.

Could you please suggest a way out?

Thanks

[Updated on: Thu, 24 April 2008 06:52]

Report message to a moderator

Re: Mutating Trigger [message #316248 is a reply to message #316246] Thu, 24 April 2008 06:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Don't you think this select is pointless because you are selecting the same column on which you are filtering or am I missing something really obvious here.
Quote:

SELECT
POB_VENDOR_CLAIM_ID
FROM
POB_VENDOR_CLAIM
WHERE
POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID AND
INACTIVE = 0



It could be something like this
UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID AND
                                 INACTIVE = 0

Having said that, in an ideal world I wouldn't be handling it this way because I am not a big fan of triggers. I will try to handle as much as I can in the application because with triggers you mask the things which are happening behind the scenes.

Regards

Raj

P.S : Please note this is untested.
Re: Mutating Trigger [message #316249 is a reply to message #316246] Thu, 24 April 2008 06:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Why do you SELECT from the updated table in the last UPDATE? Just to check, if INACTIVE column is zero in the updated row?
If so, reference the column directly:
IF :NEW.inactive = 0 THEN
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID;
END IF;


As you did not state, which table does the INACTIVE column belong to, you got two different answers for two different situations. Choose the one, which fits your case.

[Edit: Added last paragraph after seeing Raj's answer]

[Updated on: Thu, 24 April 2008 07:02]

Report message to a moderator

Re: Mutating Trigger [message #316258 is a reply to message #316249] Thu, 24 April 2008 07:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
good spot Smile

Regards

Raj
Re: Mutating Trigger [message #316265 is a reply to message #316249] Thu, 24 April 2008 07:19 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

Thanks for the replies.

Its at the times like this, when I wanna hit myself for not seeing the obvious.

Thanks so much for your inputs.

Sharath




Previous Topic: SEQEUNCE
Next Topic: PLS-00103: error
Goto Forum:
  


Current Time: Fri Dec 09 19:50:17 CST 2016

Total time taken to generate the page: 0.08085 seconds