3 Replies Latest reply: Oct 6, 2013 10:48 PM by AbdulRahimIS RSS

    TRIGGER BEFORE UPDATE

    AbdulRahimIS

      Hi, i just start learning oracle and i have a problem creating trigger to flag duplicating record.

       

      CREATE OR REPLACE TRIGGER Data.DOUBLE BEFORE UPDATE ON Data.PERSONAL FOR EACH ROW

      DECLARE

      TEMP INTEGER;

      BEGIN

      TEMP :=0;

      SELECT COUNT(*)

      INTO TEMP

      FROM PERSONAL

            WHERE (TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(MOTHER) = TRIM(:NEW.MOTHER) AND TRIM(FATHER) = TRIM(:NEW.FATHER)) OR

                  (BORN_DATE=:NEW.BORN_DATE AND TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(MOTHER) = TRIM(:NEW.MOTHER)) OR

                  (BORN_DATE=:NEW.BORN_DATE AND TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(FATHER) = TRIM(:NEW.FATHER)) ;

      IF TEMP > 0 THEN

        :NEW.STATUS:='4';

      END IF;

      END;

      /

       

      Does anyone can solve my problem?

       

      Best Regards,

      Abdul

        • 1. Re: TRIGGER BEFORE UPDATE
          Karthick_Arp

          You cant SELECT from a table on which the TRIGGER is written. Your table will be Mutating. So the trigger cannot view the data. So can you explain your requirement so that we could suggest an appropriate solution.

          • 2. Re: TRIGGER BEFORE UPDATE
            Paul  Horth

            AbdulRahimIS wrote:

             

            Hi, i just start learning oracle and i have a problem creating trigger to flag duplicating record.

             

            CREATE OR REPLACE TRIGGER Data.DOUBLE BEFORE UPDATE ON Data.PERSONAL FOR EACH ROW

            DECLARE

            TEMP INTEGER;

            BEGIN

            TEMP :=0;

            SELECT COUNT(*)

            INTO TEMP

            FROM PERSONAL

                  WHERE (TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(MOTHER) = TRIM(:NEW.MOTHER) AND TRIM(FATHER) = TRIM(:NEW.FATHER)) OR

                        (BORN_DATE=:NEW.BORN_DATE AND TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(MOTHER) = TRIM(:NEW.MOTHER)) OR

                        (BORN_DATE=:NEW.BORN_DATE AND TRIM(NAME) = TRIM(:NEW.NAME) AND TRIM(FATHER) = TRIM(:NEW.FATHER)) ;

            IF TEMP > 0 THEN

              :NEW.STATUS:='4';

            END IF;

            END;

            /

             

            Does anyone can solve my problem?

             

            Best Regards,

            Abdul

            I have a problem - can you fix it if I don't tell you what the error message is?

             

            However, I suspect you are getting a "mutating table" error. You can't select from the table you are changing.

             

            Use :old to refer to the column before the change and :new to the column after the update.

             

            Not sure about your logic though. What are you trying to achieve?

             

            In future refer to Re: 2. How do I ask a question on the forums?

            and follow the advice there.

            • 3. Re: TRIGGER BEFORE UPDATE
              AbdulRahimIS

              Thank you for all answers, now i can understood than should never use trigger before update on the same table