9 Replies Latest reply: Jan 22, 2014 4:24 AM by SKP RSS

    TRIGGER ISSUE

    Rahul_India

      Table t:

      col_name |value

      -------------   |------------

      a              |   1

       

       

      create OR REPLACE trigger tr1 
      before  update or delete on t
      for each row
      begin
      if INSERTING then
      Raise_application_error(-20002,'-invalid INSERT');
      ELSIF UPDATING THEN
      raise_application_error(-20002,'-invalid UPDATE');
      END IF;
      end;
      

       

       

      UPDATE T SET A=100 WHERE A=0

      trigger does not get called.WHY?

      UPDATE T SET A=100

      trigger does  get called.

      DELETE FROM T

      trigger does not get called.WHY?

        • 1. Re: TRIGGER ISSUE
          Hoek

          This will never be true ofcourse:

          if INSERTING then...

          since your trigger only fires before update or delete (which you're not asking in your code (IF DELETING)).


          Do you actually have data that has A=0?

          • 2. Re: TRIGGER ISSUE
            Roger

            Update 1:     It's a row trigger which is executed for every row affected by your query...no row is affected so trigger is not called.

            Delete:         The Trigger is called, but there is nothing to do for it as you have only trigger code for inserting and updating.

             

            hth

            • 3. Re: TRIGGER ISSUE
              Rahul_India

              Hi Hoek,

               

              No there is no row  where a=0.

              UPDATE T SET A=100 where a=0;

               

              Ok i got the logic since no row is being updated by the sql ,no need to call the trigger unnecessarily.

              • 4. Re: TRIGGER ISSUE
                Roger

                And ... you should be aware of the difference between a statement and a row trigger.

                 

                A statement trigger is triggered once per statement regardless of the number of rows affected (even 0).

                 

                A row trigger is triggered once per affected row.

                 

                Now in your example you try to avoid operations against the table and this would be rather a statement trigger than a row trigger as it does not depend on the content of the row but of the operation executed.

                 

                hth

                • 5. Re: TRIGGER ISSUE
                  Rahul_India

                  Roger ,

                   

                  Thank you .I was not aware of this as i have not worked with triggers so far.

                  • 6. Re: TRIGGER ISSUE
                    Karthick_Arp

                    > Now in your example you try to avoid operations against the table and this would be rather a statement trigger than a row trigger as it does not depend on the content of the row but of the operation executed.

                     

                    Even better option would be to set the proper privilege I guess

                    • 7. Re: TRIGGER ISSUE
                      Roger

                      Not a bad idea ;-)

                      • 8. Re: TRIGGER ISSUE
                        Rahul_India

                        So whats the main use of statement level trigger?

                        • 9. Re: TRIGGER ISSUE
                          SKP

                          UPDATE T SET A=100 where a=0;

                           

                          Ok i got the logic since no row is being updated by the sql ,no need to call the trigger unnecessarily.

                          You Don't Call the trigger . These called by the oracle automatically.

                          In your case the the trigger has not been called. So there is no unnecessary call