title: SQL trigger for tracking history of changes - ORACLE DB
body: <p>I have a table cust_info and need to track the update and delete on it. I created 2 new tables ( archive_customers is the same as cust_info but customers_hist contains two additional instances (changed_by and change_date)).</p>
  
  <pre><code>    CREATE TABLE CUST_INFO
      (
       CUST_ID       NUMBER(15),
       CUST_F_NAME   VARCHAR(20),
       CUST_L_NAME   VARCHAR(20),
       CUST_ADDRESS  VARCHAR(40),
       CITY          VARCHAR(30),
       STATE         VARCHAR(30),
       ZIP           NUMBER,
       PHONE         VARCHAR(12),
       PRIMARY KEY (CUST_ID)
       );
  </code></pre>
  
  <p>I was searching for similar problems and this is what I got so far:</p>
  
  <pre><code>    CREATE OR REPLACE TRIGGER TRIGGER_UPDATE
      BEFORE UPDATE ON CUST_INFO
      FOR EACH ROW
      BEGIN
      INSERT INTO CUSTOMERS_HIST
      (
       CUST_ID,
       CUST_F_NAME,
       CUST_L_NAME,
       CUST_ADDRESS,
       CITY,
       STATE,
       ZIP,
       PHONE,
       SYSDATE
       )
        SELECT 
        OLD.CUST_ID,
        OLD.CUST_F_NAME,
        OLD.CUST_L_NAME,
        OLD.CUST_ADDRESS,
        OLD.CITY,
        OLD.STATE,
        OLD.ZIP,
        OLD.PHONE
        FROM CUST_INFO 
        END;
  
        CREATE OR REPLACE TRIGGER TRIGGER_DELETE
        BEFORE DELETE ON CUST_INFO
        FOR EACH ROW
        BEGIN
        INSERT INTO ARCHIVE_CUSTOMERS
        (
        CUST_ID,
        CUST_F_NAME,
        CUST_L_NAME,
        CUST_ADDRESS,
        CITY,
        STATE,
        ZIP,
        PHONE
        )
        SELECT 
        OLD.CUST_ID,
        OLD.CUST_F_NAME,
        OLD.CUST_L_NAME,
        OLD.CUST_ADDRESS,
        OLD.CITY,
        OLD.STATE,
        OLD.ZIP,
        OLD.PHONE
        FROM CUST_INFO 
        END;
  </code></pre>
  
  <p>In addition, I am not sure how to get the change_by and change_date populated??</p>
  
  <p>If you need more info, please make a comment. </p>
  
  <p>Thank you </p>