I know what you did last summer!

Hi.

Let’s take a look at one always hot topic - automatic users activity logging…

And we are not interested in using some CUBRID Manager integrated logs solution or similar – I want a solution which does not depend on any CUBRID client type, a solution which works from whatever client the users will use to connect to a CUBRID database.

I want to be able to track what the user did – the INSERTs, the DELETEs, and the UPDATEs which were executed in my database.

So how do we do that…?

Well, it’s pretty obvious, the only easy way this would work is by using TRIGGERs!

Let’s simplify the problem a little bit – let’s talk about just one single table and let’s use the nation table from the demodb database.

First, let’s create a table which will hold the users’ activity log – nation_log:

Next thing – a visit to CUBRID Triggers tutorial!

After reading the tutorial and looking into online CUBRID TRIGGER manuals, it’s pretty clear what we have to do:

• Create one TRIGGER for each SQL operation type: INSERT, UPDATE, DELETE
• In each of these triggers, execute a custom SQL statement that updates the nation_log log table with the relevant operation information

Let’s start with the INSERT trigger and let’s log only two of the nation’s columns – [code] and [name], for example:

Ok - it’s time now to do some testing:

As you can see, the INSERT was intercepted by our trigger and the event data was saved into our logging table – which is exactly what we needed! :)

One more example – catching an UPDATE event:

I will leave the DELETE up to you, it’s not that complicated at all…

And this is it – now I know what you did in my database, so don’t lie to me! :)

Btw, these simple logging examples can be improved in so many ways…! For example:

• Log user name
• Log user IP address
• Log operation timestamp
• etc...

See you next time!

/Dee

P.S. Have you seen this application: http://sourceforge.net/projects/triggeraudit/...?