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...
/Dee
P.S. Have you seen this application: http://sourceforge.net/projects/triggeraudit/...?
