CODEWITHSUNDEEP
sqloracle-databasetriggers
user3131067
user3131067

Reputation: 15

SQL trigger for tracking history of changes - ORACLE DB

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)).

    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)
     );

I was searching for similar problems and this is what I got so far:

    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;

In addition, I am not sure how to get the change_by and change_date populated??

If you need more info, please make a comment.

Thank you

Upvotes: 2

Views: 4696

Answers (1)

Rachcha
Rachcha

Reputation: 8816

Perhaps you could use the user and sysdate built-in functions for chnage_by and change_date respectively. The code I would write is completely different if I want to store the change history in a table. Yet, for your example, the code is as follows:

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,
                                change_by,
                                change_date)
        SELECT OLD.CUST_ID,
               OLD.CUST_F_NAME,
               OLD.CUST_L_NAME,
               OLD.CUST_ADDRESS,
               OLD.CITY,
               OLD.STATE,
               OLD.ZIP,
               OLD.PHONE,
               user,
               sysdate
          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,
                                   change_by,
                                   change_date)
        SELECT OLD.CUST_ID,
               OLD.CUST_F_NAME,
               OLD.CUST_L_NAME,
               OLD.CUST_ADDRESS,
               OLD.CITY,
               OLD.STATE,
               OLD.ZIP,
               OLD.PHONE,
               user,
               sysdate
          FROM CUST_INFO;
END;
/

That was the answer for your question.

Now, if I were you, I would build an audit table as follows:

TABLE audit_entry
(
    audit_Entry_Id   INTEGER,
    change_By        VARCHAR2 (30), -- User who made the change
    change_TS        TIMESTAMP,     -- TS at which the change was made
    table_Name       VARCHAR2 (30), -- Name of table on which change was made
    column_Name      VARCHAR2 (30), -- Name of column in that table
    primary_key_id   INTEGER,       -- The PK or ID of the row which was changed
    old_clob         CLOB,
    new_clob         CLOB,
    old_TS           TIMESTAMP,
    new_TS           TIMESTAMP,
    old_number       NUMBER (36, 2),
    new_number       NUMBER (36, 2)
)

The last old and new columns would store the old and new values of appropriate data type. You could also improve the table as per your needs. Then, build a trigger on your tables to track changes and insert values into audit_entry table accordingly.

Upvotes: 3

Related Questions