Friday 27 March 2009

Easy_Logger for Oracle - Log every insert, update or delete for a table , with details

When it comes to client's demands when creating some IS or designing a DB, very often there's a need to have a complete log of all actions on some table containing important data.
I received a few such demands myself...

Something like this isn't so hard to make it yourself.

We could easily write a
before insert or update or delete trigger and iterate through all the columns saving all old and new values into some log table.
But what if we have many tables that needs such log? Writing all of these triggers would take a quite time...
And what happens when we alter some of these tables?

That is why I created "Easy_Logger".
Thanks to Easy_Logger we can easily create and maintain logging triggers.

Easy_logger is a PL/SQL package , so it can be applied only to Oracle.
It is written in Oracle 10g. I didn't test it on any other version...

So, how do we use Easy_Logger?

First of all, you should run this script:

instal_EL_prerequisites.sql

It will create a table called "EL" ( as Easy Logger ) which will contain all of our logs.
It has 6 columns:

EL_ID - autoincrementing ID , primary key
EL_TABLE - Table name for which the log is created
EL_ACTION - Which action was logged ( insert, update or delete )
EL_LOG - The log itself containing all the old and/or new values in a record
EL_USER - User name which commited action
EL_DATE - When was log created

If this script ended without errors, you can now download Easy_Logger package itself and apply it into your database:

Package declaration
Package body


If everything went well, and without errors, you can now start creating a logging trigger for any table you wish.
I will explain now how to do this:

When creating an Easy_logging trigger for some table, you need to specify some parameters , that are part of a record EL_PREFERENCES:

it looks like this:

TYPE EL_PREFERENCES IS RECORD (
table_name varchar2(64),
create_AFTER_trigger BOOLEAN := true,
on_insert BOOLEAN := true,
on_update BOOLEAN := true,
on_delete BOOLEAN := true
);

so, if you want to create an AFTER [{INSERT},{UPDATE},{DELETE}] TRIGGER, you would set "create_AFTER_trigger" variable to TRUE ( else, if you would like to create a BEFORE [{INSERT},{UPDATE},{DELETE}] TRIGGER, you would set "create_AFTER_trigger" variable to FALSE) .
"Table_name" variable holds information about table's name for which you want to create a trigger,
and "on_insert" , "on_update" and "on_delete" simply tells Easy_logger on which action should a trigger fire.
After you set all of this variables, you need to call a procedure called EASY_LOGGER.CREATE_TRIGGER() by sending this PL/SQL record to it...




EXAMPLE:

if we want to make a trigger that fires on after inserting or deleting a record in a table called MY_TABLE, we would execute following code:

declare
preferences EASY_LOGGER.EL_PREFERENCES;
begin
preferences.table_name := 'MY_TABLE';
preferences.create_AFTER_trigger := true;
preferences.on_insert := true;
preferences.on_update := false;
preferences.on_delete := true;
EASY_LOGGER.CREATE_TRIGGER(preferences); <-- this is the procedure for creating a trigger!
end;

or just call

exec EASY_LOGGER.CREATE_TRIGGER('MY_TABLE',true,true,false,true);

(notice that arguments for this procedure are exactly in the same order as variables in EL_PREFERENCES record)

and a code for creating a logging trigger will generate in dbms_output, so don't forget to set serveroutput on!

All that there is left for you to do is to execute generated code, and you'll have your logging trigger for MY_TABLE table created.

For those who need generated code as a returning value, and not as dbms_output output, there is a function called get_trigger_ddl which has no input parameters.
It returns last generated trigger's  DDL .

You can call it like this:

declare
your_variable clob;
begin
EASY_LOGGER.CREATE_TRIGGER('MY_TABLE',true,true,true,true);
your_variable := EASY_LOGGER.get_trigger_ddl();
...  -- do whatever you like with generated code...
end;

That's it!
Every action specified when creating logging trigger will be logged in EL table.
I hope you'll like it...
You are free to report any bug, or suggest any good idea for me to implement in order to make Easy_logger better...

4 comments:

Dejan Topalovic said...

Dobar tekst - svaka cast!

Imao bih par prijedloga u vezi poboljsanja performansi:

1. Sekvencu kreiraj sa CACHE 100 ili cak jos vise. Ako imas RAC bazu, onda OBAVEZNO moras kreirati sekvencu sa CACHE 500 ili vise, pogotovo ako vise aplikacija istovremeno koriste Easy Logger.

2. Nemoj koristiti trigger za popunjavanje EL_ID kolone, nego ubaci ovaj izraz:
SELECT EL_SEQ.NEXTVAL INTO tmpVar FROM dual;
negdje u Easy Logger package body


Vjeruj mi da ces i sam primijetiti razliku, kada istovremeno nekoliko aplikacija pocne da koristi taj nacin logovanja...

Osim toga, kad vec zelis podijeliti source code s drugima, onda nemoj stavljati wrapped source code, jer ti niko nece to instalirati. :)

Ja sam kod nas napravio jedan Logging alat, koji koristi globalne varijable i temporary PL/SQL tabele, pa cu okaciti to kao Open Source alat cim uhvatim vremena da sredim dokumentaciju i jos par stvarcica...

Darko Kalinic said...

Zdravo Dejane.

Drago mi je da ti se svidio tekst.

Sto se tice tvojih predloga:

1. Potpuno si u pravu za CACHE. Vec danas cu ispraviti kreiranje sekvence. Upravo cu ovu alatku uskoro koristiti na RAC-u.

U pravu si i za drugi prijedlog, jer zaista bih smanjio koriscenje resursa kada bih koristio jedan INSERT INTO za ubacivanje svih kolona.

Problem je u tome sto sam za kreiranje sekvence i tabele "EL" iskoristio skript iz jedne baze koja je jos iz vremena kada sam osmisljao kako da mi izgleda novi sistem za logovanje.

A zbog velikog broja obaveza nisam imao ni vremena ni zelje da sve optimizujem pa sam iskoristio ono sto sam imao.

A sto se tice source code-a, sto da ne...?
Mogao bih staviti kasnije u toku dana i kod...

Hvala puno na konstuktivnim predlozima!

Anonymous said...

Very nice, thanks for that.
But: For me it would be useful to add the query itself to the logging. So i tried to get the last sql, it works with
"select * from v$sql where first_load_time=(select max(first_load_time) from v$sql"
on my local dev-environment. But:I think i cannot be shure that this command was the one that lead to the logtable-entry. How can i complete your solution with the right sql-statement?

Darko Kalinic said...

Why don't you try some of these links:

http://www.oracle.com/technology/oramag/code/tips2005/100305.html

http://www.oracle-training.cc/oracle_tips_v$sql.htm

I think, this might help you...