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...