problem with update statement

From: KumarD <dil_maj_at_hotmail.com>
Date: 15 Nov 2001 11:59:12 -0800
Message-ID: <d0634c75.0111151159.da33df@posting.google.com>

I have written triggers on 2 tables. trigger on table A updates the table B and table B's trigger updates table A.

I am avoiding mutating trigger effect by identifying the value of the column.
if the new value of del_flag is '~', trigger won't execute the update statement for another table otherwise it will.

The code looks like this.

Create or Replace Trigger T_UPD_TableB
BEFORE UPDATE ON TableA
FOR EACH ROW
BEGIN
-- tableB updating tableA

   IF :NEW.DEL_FLAG = '~' THEN

• restore old value :NEW.DEL_FLAG := :OLD.DEL_FLAG ; RETURN ; END IF ;
  • update the value in TableB UPDATE TableB SET updateCol = 'C', DEL_FLAG = '~' WHERE ID = :new.Deal_id; END ; /

    ---

Create or Replace Trigger T_UPD_TableA
BEFORE UPDATE ON TableB
FOR EACH ROW
BEGIN
-- tableA updating TableB

   IF :NEW.DEL_FLAG = '~' THEN

	:NEW.DEL_FLAG := :OLD.DEL_FLAG ;
        Return ;

   END IF ;
--tableb updating tablea

	UPDATE TableA
	SET updateCol = 'C',
	DEL_FLAG = '~'
	WHERE DEAL_ID = :new.Deal_id;

END ;
/

---

The Problem is :
It works fine when TableA is updated.
But when I update tableB, it gives mutating trigger error.

To see what's happening I commented all code and updated the value of del_Flag in 3rd table tableC.

So the new code looks like this:

Create or Replace Trigger T_UPD_TableA
BEFORE INSERT OR UPDATE ON TableB
FOR EACH ROW
BEGIN
-- trace the new values of column del_Flag and updateCol

   update tableC

     set UpdateCol := :new.Updatecol,
           del_flag := :new.del_flag  ;

  Return ;
END ; Surprisingly the value of del_Flag remains unchanged, but the value of another column updateCol is updated properly.

any guess why? Received on Thu Nov 15 2001 - 13:59:12 CST