2 Replies Latest reply: Oct 8, 2013 2:44 AM by xysOra RSS

    BEFORE UPDATE trigger between two tables' field

    xysOra

      Hi Oracle Experts,

       

      I'm a newbie in Triggers. I want to make a trigger that fires whenever changes made to a table, will update a field in the second table. The details are as below:

       

       SAMPLE (
      SAMPLEID    NUMBER(10,0)
      ACTIVITYID     NUMBER(10,0)
      ACTIVITYTABLEID     VARCHAR2(20 BYTE)
      SAMPLEDT     DATE
      SAMPLEPTID     VARCHAR2(20 BYTE)
      SAMPLENOTIFY     VARCHAR(1 BYTE)
      )
      

       

      SAMPLE_RESULT(
      SAMPLEID     NUMBER(10,0)
      TESTID     VARCHAR2(20 BYTE)
      PROPERTYID     VARCHAR2(20 BYTE)
      TESTERID     VARCHAR2(20 BYTE)
      ENTRYDT     DATE
      RESULTNUM     NUMBER(18,8)
      RESULT     VARCHAR2(20 BYTE)
      RESULTTYPE     VARCHAR(1 BYTE)
      RESULTSTATUS     VARCHAR2(1 BYTE)
      )
      

       

      The idea of the trigger:

       

      CREATE OR REPLACE
      TRIGGER "DBA".AUTO_NOTIFY
      BEFORE UPDATE OF RESULT ON SAMPLE_RESULT
      FOR EACH ROW
      WHEN (NEW.PROPERTYID = 'Action Taken')
      BEGIN
      
      //my idea
      :new.sample.samplenotify = 'S'
      
      //but i don't know how to make a reference to another table
      
      END;
      

       

      The trigger will fires whenever the propertyid is Action Taken, it will set the value of samplenotify in table sample to 'S'.

       

      Is it  possible?

       

      Thank you in advance.