Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    3

    Unanswered: Before Delete trigger throwing a mutating error

    Hello,
    I am trying to create a trigger that before deletion checks other child instances (where parent_id equals the same value) for a particular value. If this value does or does not exist then update the parent on the result.

    Below is the trigger:
    Code:
    CREATE OR REPLACE TRIGGER Z_TRIGGER_1 
    
      BEFORE DELETE ON CHILD_TABLE 
      FOR EACH ROW
    
     DECLARE
        PRAGMA AUTONOMOUS_TRANSACTION; 
    	
    BEGIN
    
            UPDATE PARENT_TABLE 
            SET NATIVE_ENTITLEMENT = (
    		SELECT   CASE WHEN COUNT(child.ID) > 0 THEN 1 ELSE 0 END          
    		FROM CHILD_TABLE child        
    		WHERE child.PARENT_ID = :OLD.PARENT_ID
    		AND child.NATIVE_ENTITLEMENT = 1)
            WHERE ID = :OLD.PARENT_ID;
    END;
    When I try and delete an instance it throws a mutation error, what can I do for this trigger to do what I want?

    Any help will be appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >When I try and delete an instance it throws a mutation error,
    This error results when within the trigger code is a SQL statement that acesses the same table upon which the trigger is based.
    To eliminate the error, do not issue SQL against same table upon which the table is based.

    Consider using MERGE statement in place of the flawed trigger.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2014
    Posts
    3
    Is this the correct syntax for Merge, never used this before:
    Code:
    CREATE OR REPLACE TRIGGER Z_TRIGGER_1 
    
      BEFORE DELETE ON CHILD_TABLE 
      FOR EACH ROW
    
     DECLARE
        PRAGMA AUTONOMOUS_TRANSACTION; 
    	
    BEGIN
    
            MERGE INTO PARENT_TABLE P
    		USING (SELECT  PARENT_ID           
    				FROM CHILD_TABLE child        
    				WHERE child.PARENT_ID = :OLD.PARENT_ID
    				AND child.VAR_1 = 1) child
    		ON (p.ID = child.PARENT_ID)
    		WHEN MATCHED THEN UPDATE SET P.VAR_1 = 1
    		WHEN NOT MATCHED THEN UPDATE SET P.VAR_1 = 0;
    END;
    The logic I am after is, if there is at least 1 child which var_1 equals 1 then the parent should have this value else if all children instances have var_1 equaling 0 then the parent should reflect this.
    Last edited by leose02; 05-14-14 at 02:44.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You may NOT use a on each row trigger that will access the same table to trigger is on. It is not allowed by oracle. Wither you use update or merge makes no difference. It is NOT allowed. and the use of "PRAGMA AUTONOMOUS_TRANSACTION;" should be reserved for something like logging and never used for normal processing. It is a very bad idea.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •