Database table history with triggers

Here is a short sample about keeping all data of a table within a history table. The way I prefer it to do is via database triggers. Lets assume you have a table called:

CREATE TABLE BOOK {

• BOOK_ID       NUMBER(10),
• NAME             VARCHAR2(10),
• AUTHOR         VARCHAR2(10),
• ISBN                VARCHAR2(13)

}

Your application as well as your database scripts are working on that table. Your aim is to keep track on all changes on that table. To do so you need a history table, which looks pretty much the same as the original table and you have to add to fields to your original table:

CREATE TABLE BOOK {

• BOOK_ID                NUMBER(10),
• NAME                      VARCHAR2(10),
• AUTHOR                  VARCHAR2(10),
• ISBN                         VARCHAR2(13)
• CHANGE_DATE      DATE,
• CHANGE_USER      VARCHAR2(20)

}

CREATE TABLE BOOK_HISTORY {

• BOOK_ID                NUMBER(10),
• NAME                      VARCHAR2(10),
• AUTHOR                  VARCHAR2(10),
• ISBN                         VARCHAR2(13)
• CHANGE_DATE      DATE,
• CHANGE_USER      VARCHAR2(20),
• ACTION                   VARCHAR2(100)

}
and create db triggers for all manipulations to the original table:

CREATE OR REPLACE TRIGGER BOOK_INSERT
       BEFORE INSERT ON BOOK
       FOR EACH ROW

BEGIN

   INSERT INTO BOOK_HISTORY
                    (BOOK_ID, NAME, AUTHOR, ISBN, 
                     CHANGE_DATE, CHANGE_USER, ACTION)
    VALUES (:new.BOOK_ID,:new.NAME, :new.AUTHOR, :new.ISBN, 
                      :new.CHANGE_DATE, :new.CHANGE_USER, 'INSERTED');
 END;


CREATE OR REPLACE TRIGGER BOOK_CHANGE
       BEFORE UPDATE ON BOOK
       FOR EACH ROW

BEGIN

   INSERT INTO BOOK_HISTORY
                    (BOOK_ID, NAME, AUTHOR, ISBN, 
                     CHANGE_DATE, CHANGE_USER, ACTION)
    VALUES (:old.BOOK_ID, :old.NAME, :old.AUTHOR, :old.ISBN, 
                      :old.CHANGE_DATE, :old.CHANGE_USER, 'CHANGED');
 END;

CREATE OR REPLACE TRIGGER BOOK_DELETE
       BEFORE DELETE ON BOOK
       FOR EACH ROW

BEGIN

   INSERT INTO BOOK_HISTORY
                    (BOOK_ID, NAME, AUTHOR, ISBN, 
                     CHANGE_DATE, CHANGE_USER, ACTION)
    VALUES (:old.BOOK_ID, :old.NAME, :old.AUTHOR, :old.ISBN, 
                      :old.CHANGE_DATE, :old.CHANGE_USER, 'DELETED');
 END;


These are three simple triggers which will fire if someone (application, scripts or whatever) will change the book table. You might write more sophisticated triggers like WHERE clauses or a CHANGE_FIELD and CHANGE_VALUE column.