Wednesday, 2 September 2015

How to create history table in oralce

Summary
This article describes a classic method of capturing table changes (insert, updates, deletes). Table changes are stored in another table along with user information who did the change.

Create table <Base Table Name>  and the table for history changes <History Table Name EX:- Table_Name_H>

CREATE TABLE <Base Table Name>
AS SELECT * FROM (Another Table Name);

CREATE TABLE <History Table Name EX:- Table_Name_H> 
AS SELECT * FROM <Base Table Name>
WHERE 1=2;

ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (USERNAME   VARCHAR2(30));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (OSUSER   VARCHAR2(30));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (MACHINE   VARCHAR2(64));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (PROGRAM   VARCHAR2(48));
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (LOGON_TIME   DATE);
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (CHANGE_TIME   DATE);
ALTER TABLE <History Table Name EX:- Table_Name_H> ADD (CHANGE_TYPE   VARCHAR2(10));

TRIGGER CREATION PROCESS:-
====================================

/* Formatted on 9/2/2015 9:12:30 AM (QP5 v5.240.12305.39446) */
CREATE OR REPLACE TRIGGER [Trigger name]
   BEFORE INSERT OR DELETE OR UPDATE
   ON <Base Table Name>
   FOR EACH ROW
DECLARE
   username1     VARCHAR2 (30);
   osuser1       VARCHAR2 (30);
   machine1      VARCHAR2 (64);
   program1      VARCHAR2 (48);
   logon_time1   DATE;
BEGIN
   SELECT USERNAME,
          OSUSER,
          MACHINE,
          PROGRAM,
          LOGON_TIME
     INTO USERNAME1,
          OSUSER1,
          MACHINE1,
          PROGRAM1,
          LOGON_TIME1
     FROM v$session
    WHERE audsid = (SELECT USERENV ('SESSIONID') FROM DUAL);

   IF INSERTING
   THEN
      INSERT INTO <History Table Name EX:- Table_Name_H>
                                     (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:NEW.OWNER,
                   :NEW.OBJECT_NAME,
                   :NEW.SUBOBJECT_NAME,
                   :NEW.OBJECT_ID,
                   :NEW.DATA_OBJECT_ID,
                   :NEW.OBJECT_TYPE,
                   :NEW.CREATED,
                   :NEW.LAST_DDL_TIME,
                   :NEW.TIMESTAMP,
                   :NEW.STATUS,
                   :NEW.TEMPORARY,
                   :NEW.GENERATED,
                   :NEW.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'INS');
   ELSIF DELETING
   THEN
      INSERT INTO<History Table Name EX:- Table_Name_H>
                                    (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:OLD.OWNER,
                   :OLD.OBJECT_NAME,
                   :OLD.SUBOBJECT_NAME,
                   :OLD.OBJECT_ID,
                   :OLD.DATA_OBJECT_ID,
                   :OLD.OBJECT_TYPE,
                   :OLD.CREATED,
                   :OLD.LAST_DDL_TIME,
                   :OLD.TIMESTAMP,
                   :OLD.STATUS,
                   :OLD.TEMPORARY,
                   :OLD.GENERATED,
                   :OLD.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'DEL');
   ELSIF UPDATING
   THEN
      INSERT INTO <History Table Name EX:- Table_Name_H>
                                     (OWNER,
                                     OBJECT_NAME,
                                     SUBOBJECT_NAME,
                                     OBJECT_ID,
                                     DATA_OBJECT_ID,
                                     OBJECT_TYPE,
                                     CREATED,
                                     LAST_DDL_TIME,
                                     TIMESTAMP,
                                     STATUS,
                                     TEMPORARY,
                                     GENERATED,
                                     SECONDARY,
                                     USERNAME,
                                     OSUSER,
                                     MACHINE,
                                     PROGRAM,
                                     LOGON_TIME,
                                     CHANGE_TIME,
                                     CHANGE_TYPE)
           VALUES (:OLD.OWNER,
                   :OLD.OBJECT_NAME,
                   :OLD.SUBOBJECT_NAME,
                   :OLD.OBJECT_ID,
                   :OLD.DATA_OBJECT_ID,
                   :OLD.OBJECT_TYPE,
                   :OLD.CREATED,
                   :OLD.LAST_DDL_TIME,
                   :OLD.TIMESTAMP,
                   :OLD.STATUS,
                   :OLD.TEMPORARY,
                   :OLD.GENERATED,
                   :OLD.SECONDARY,
                   USERNAME1,
                   OSUSER1,
                   MACHINE1,
                   PROGRAM1,
                   LOGON_TIME1,
                   SYSDATE,
                   'UPD');
   END IF;
END;
/

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete