Donnerstag, 26. Januar 2012

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.