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.
