0

To understand an application it would be quite helpful if every database change could be logged somehow. Then you can execute an action on the frontend and see what happens in the database. (I only care about the last 5 minutes, or so, but more would not hurt.) What possibilities exist for that?

I know it is possible to configure the JDBC driver to log the executed statements, but it logs rather more than I want to see (I don't care about queries, for instance) and is mixed wildly into your logfiles. :-/

Another thing I can think of is to create triggers for each table that write data on the changes into a logging-table. Did anyone manage to do that? Especially creating a script that creates those triggers for a given set of tables?

Community
  • 1
  • 1
Dr. Hans-Peter Störr
  • 25,298
  • 30
  • 102
  • 139

2 Answers2

0

You can find many examples of triggers in Oracle documentation. What you need is after insert, update, delete for each row triggers to populate your log tables. Here's some example from Oracle docs:

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS020

CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));

 CREATE OR REPLACE TRIGGER Log_salary_increase_ARUID
  AFTER UPDATE OR INSERT OR DELETE ON emp
  FOR EACH ROW
BEGIN
  -- Can be separated for Inserting then Updating with addl if
  -- In this case it may be easier to control and/or add flags to your log tables
  -- such as Action = 'INS' or Action = 'UPD' --
  If (INSERTING OR UPDATING) 
  THEN
    -- Insert newly created/updated values to your log table --
    INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
     VALUES (:NEW.Empno, SYSDATE, :NEW.SAL, 'INS_UPD');
  ELSE  
    -- Deleting - insert old or deleted values to your logs --
   INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
     VALUES (:OLD.Empno, SYSDATE, :OLD.SAL, 'DEL');
END;
/
Art
  • 5,616
  • 1
  • 20
  • 22
  • Couple of suggestions -- you might want to use a timestamp instead of a date, and naming the salary column new_salary might not be a good choice if a delete is going to log the "old" salary. – David Aldridge Mar 13 '13 at 11:34
  • @David - thanks. I copied this from Oracle docs with little modifications. This is general example. It is up to the creator to define columns etc... – Art Mar 13 '13 at 12:41
0

With the triggers you can just keep the previous data (a history of your data). why you don't simply insert your logs in a log table?

Dax
  • 438
  • 1
  • 8
  • 29