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

My N.A.N.Y. 2009 entry - generator for creating document containing info about all user tables and constrains in oracle

For this post, I decided to tell you a little bit more about my N.A.N.Y. 2009 entry.

As I said on donationcoder.com , I started writing this application because I needed to create a document at work, just like the one that this application generates.
For the past year, I was working on a large project , and at one point – I needed to provide a complet list of all tables and constraints for several users in DB that we created...
I, maybe, could use some existing application to do this, but I wanted to make it myself
;-) . Specially when half of work was already done, because I used queries provided by Olivera at www.insidedev.blogspot.com



So, how do we use it?
First of all, download it from
http://www.kdarko.users.t-com.me/downloads/MGTD.zip
and extract all files and folders to some installation dir.

To start the application, run “RUN MGTD.bat” and a login window will appear.

enter all parameters and click “connect”.

New window, containing all user tables will appear.

Here , we can decide which tables we want to process. We can do that by applying certain filter , or just by excluding some tables.

Next thing we need to do is to set some things. We do this in “settings” tab.
Here we can select what kind of constraints we want to generate information for.

Also, we can select which colors to use when generating document.

I’ll use default colors, except for table containing colums of current table.
I’ll use yellow for it.

And, before we start generating a document, we need to define a location for our document …
I’ll use c:\MGTD

When you’re ready – press Execute.

And when progress bar reaches 100%, you will have your document created…

Every table is described ( what columns it contains , what data type ( ans size of it) , nullable? information and a comment ) and there is also a list of all constraints for that table ( Primary keys, Checks, Foreign keys and Unique keys ).

As you can see, table containing info about columns names and type are in yellow, like we wanted it to be…

Now when we have html document, we can easily create a .doc or .pdf …

Not many people will find it usefull, but for those who need something like it, it's more than helpfull, and I hope they'll like it...