4 Replies Latest reply: Feb 26, 2013 11:40 PM by jeneesh RSS

    After update trigger

    user-1221
      Hi,

      We have one table which is copied to another schema and added some new columns to it,
      SQL>desc Source.content
      
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ID                                        NOT NULL NUMBER(9)
       RESOURCEID                                NOT NULL NUMBER(9)
       APPOINTMENTDATE                           NOT NULL DATE
       STARTTIME                                          DATE
       ENDTIME                                            DATE
       STATUS                                             VARCHAR2(20)
      MASTERRESOURCEID                                   NUMBER(9) 
      REFERENCEID                               NOT NULL NUMBER(9)
       PROPREF                                            NUMBER(9)
      
      SQL> desc target.content
      
      Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       ID                                        NOT NULL NUMBER(9)
       RESOURCEID                                NOT NULL NUMBER(9)
       APPOINTMENTDATE                           NOT NULL DATE
       STARTTIME                                          DATE
       ENDTIME                                            DATE
       STATUS                                             VARCHAR2(20)
      MASTERRESOURCEID                                   NUMBER(9) 
      REFERENCEID                               NOT NULL NUMBER(9)
       PROPREF                                            NUMBER(9)
       OLD_STARTDATE                                      DATE  
       OLD_STARTTIME                                      DATE
       OLD_RESOURCEID                                     NUMBER(9)
       OLD_MASTERRESOURCEID                               NUMBER(9)
       LAST_UPDATE_DATE                                   DATE
       ACTION_FLAG                                        VARCHAR2(20)
      I have created one "After Insert" trigger on the source.content table, to insert the data to target.content table after each Insert transaction happened on Source.content, this trigger is working fine and the target.content table is getting all the new rows in it as well.

      Now I want to implement the same thing in Updates as well, after any update on source.content table, it should be reflected in target.content table along with the new set of columns should have the old values.

      •     OLD_STARTDATE: Old APPOINTMENTDATE of the record if updated.
      •     OLD_STARTTIME: Old STARTTIME of the record if updated.
      •     OLD_ RESOURCEID: Old RESOURCEID of the record if updated.
      •     OLD_MASTERRESOURCEID: Old MASTERRESOURCEID of the record if updated.
      •     LAST_UPDATE_DATE: This will be the timestamp of the insert/update transaction to this table
      •     ACTION_FLAG:
      NEW: If this is a new appointment in the system
      UPDATE: If an existing appointment is being updated in the system

      Kindly help me in writing this trigger,
      Thanks in advance.
        • 1. Re: After update trigger
          sb92075
          user-1221 wrote:
          Hi,

          We have one table which is copied to another schema and added some new columns to it,
          SQL>desc Source.content
          
          Name                                      Null?    Type
          ----------------------------------------- -------- ----------------------------
          ID                                        NOT NULL NUMBER(9)
          RESOURCEID                                NOT NULL NUMBER(9)
          APPOINTMENTDATE                           NOT NULL DATE
          STARTTIME                                          DATE
          ENDTIME                                            DATE
          STATUS                                             VARCHAR2(20)
          MASTERRESOURCEID                                   NUMBER(9) 
          REFERENCEID                               NOT NULL NUMBER(9)
          PROPREF                                            NUMBER(9)
          
          SQL> desc target.content
          
          Name                                      Null?    Type
          ----------------------------------------- -------- ----------------------------
          ID                                        NOT NULL NUMBER(9)
          RESOURCEID                                NOT NULL NUMBER(9)
          APPOINTMENTDATE                           NOT NULL DATE
          STARTTIME                                          DATE
          ENDTIME                                            DATE
          STATUS                                             VARCHAR2(20)
          MASTERRESOURCEID                                   NUMBER(9) 
          REFERENCEID                               NOT NULL NUMBER(9)
          PROPREF                                            NUMBER(9)
          OLD_STARTDATE                                      DATE  
          OLD_STARTTIME                                      DATE
          OLD_RESOURCEID                                     NUMBER(9)
          OLD_MASTERRESOURCEID                               NUMBER(9)
          LAST_UPDATE_DATE                                   DATE
          ACTION_FLAG                                        VARCHAR2(20)
          I have created one "After Insert" trigger on the source.content table, to insert the data to target.content table after each Insert transaction happened on Source.content, this trigger is working fine and the target.content table is getting all the new rows in it as well.
          so please post trigger source code
          • 2. Re: After update trigger
            user-1221
            After Insert Trigger on Source.content table
            CREATE OR REPLACE TRIGGER sync_new_rows
              AFTER INSERT ON source.content
              FOR EACH ROW
            BEGIN
              INSERT INTO target.content
                     (ID, RESOURCEID, APPOINTMENTDATE, STARTTIME, ENDTIME, STATUS, MASTERRESOURCEID, PROPREF,LAST_UPDATE_DATE,ACTION_FLAG)
                     VALUES
                     (:new.ID,:new.RESOURCEID, :new.APPOINTMENTDATE, :new.STARTTIME, :new.ENDTIME, :new.STATUS,  :new.MASTERRESOURCEID, :new.PROPREF,sysdate,'NEW');
            END sync_new_rows;
            /
            
             SQL> insert into source.content(id,resourceid,appointmentdate,referenceid) values(888888,88888,sysdate-2,8888);
            
            1 row created.
            
            SQL> select id,resourceid,appointmentdate,referenceid,last_update_date,action_flag from target.content;
            
                    ID RESOURCEID  APPOINTME REFERENCEID   LAST_UPDATE_DATE ACTION_FLAG
            ---------- ---------- ---------- --------- --------- ---------  
               888888      88888       24-FEB-13     8888       26-FEB-13   NEW
            Edited by: user-1221 on Feb 27, 2013 8:40AM
            • 4. Re: After update trigger
              jeneesh
              Duplicate post deleted

              Edited by: jeneesh on Feb 27, 2013 11:10 AM