Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    3

    Unanswered: Mutating trigger.

    Hi All ,

    I am new to oracle , we have migrated our Sybase db to Oracle11g.

    Now we are facing Mutating problem in triggers. To resolve the mutating problem in trigger we have written Compound trigger but our problem is that we want to first fire the BEFORE EACH ROW statement first and then BEFORE STATEMENT trigger in COMPOUND trigger , i don't know whether we can do this or not but i need your suggestion, the reason why i need to do this is that we want to validate the data at ROW level first and once it pass the validation we want to fire the BEFORE statement.


    Please help!!!!!

    Thank you all in advance
    Last edited by hemant_patel12; 10-26-12 at 03:38. Reason: Removing old example

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >EXCEPTION
    >WHEN OTHERS THEN
    >NULL;
    >END;

    whoever wrote such a blatant BUG, should be terminated for incompetence.


    delete, remove, & eliminate all EXCEPTION code
    For reason why check these links.

    The Tom Kyte Blog: The dreaded "when others then null" strikes again...

    The Tom Kyte Blog: Why do people do this?

    The Tom Kyte Blog: A challenge!
    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
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    Hi.
    I think you should specify and tell us exactly what you want. A trigger may not be the best solution.
    Your trigger seems like an automatic conversion of sqlanywhere code to oracle, but a good choice with sqlanywhere is not necessarily good with oracle.

    By the way, in oracle triggers fires in the following order :
    If two or more triggers with different timing points are defined for the same statement on the same table, then they fire in this order:

    All BEFORE STATEMENT triggers
    All BEFORE EACH ROW triggers
    All AFTER EACH ROW triggers
    All AFTER STATEMENT triggers

    You can also simplify a lot your code, for example :

    Code:
    BEGIN
    		SELECT 1 INTO v_temp
    		FROM DUAL
    		WHERE ( SELECT :NEW.ACTIVE
    			FROM DUAL ) = 'c';
    		EXCEPTION
    		WHEN OTHERS THEN
    			NULL;
    	END;
    
    	IF v_temp = 1 THEN
    is almost equivalent ( minus the when others bug ) to :
    Code:
        IF :NEW_ACTIVE = 'c' THEN
    regards,
    Eric.

Posting Permissions

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