2 Replies Latest reply: Feb 20, 2013 4:41 PM by Frank Kulash RSS

    trigger fires only when a specific row get updated--how to create

    kp2000
      create or replace trigger test
      AFTER
      UPDATE OF VALUE ON table_name
      where column_c1_value=100 --***


      FOR EACH ROW

      BEGIN
      --------------

      end;

      I am getting error at this place..***

      How i can write this so that this trigger fires only when prticular row with value=100 get updated.

      thanks
      kp
        • 1. Re: trigger fires only when a specific row get updated--how to create
          JustinCave
          First, are VALUE and COLUMN_C1_VALUE supposed to be the same column name?

          Second, are you trying to fire the trigger when COLUMN_C1_VALUE is updated to 100? Or from 100? Or do you want the trigger to fire when COLUMN_C1_VALUE is 100 whether or not the COLUMN_C1_VALUE value is changed?

          Justin
          • 2. Re: trigger fires only when a specific row get updated--how to create
            Frank Kulash
            Hi,
            user8612301 wrote:
            create or replace trigger test
            AFTER
            UPDATE OF VALUE ON table_name
            where column_c1_value=100 --***
            The keyword you want is WHEN (not WHERE), and you have to specify if you're checking the NEW.column_c1 value or the OLD.column_c1 value. (I assume the column name is column_c1, not column_c1_value).
            See the PL/SQL manual for the exact syntax and details, including examples.

             

            I hope this answers your question.
            If not, post a complete test script that people can run to re-create the problem and test their ideas. Include your best attempt at a trigger, a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, 1 or 2 UPDATE statements that cause the trigger to fire, and 1 or 2 that don't, and also post the results you want from that data.
            The sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}