Pete Finnigan's Oracle Security Weblog

I saw an interesting post on the Amis Blog today written by Andre Crone titled "Create logging data in the same table" where Andre talks about the problem of logging every update on a table but storing the results in the same table. As Andre points out this cannot be done with one trigger as the mutating table problem rears its head. He goes on to discuss how the problem can be solved with three triggers and a package. Andre gives some example code as well including a sample test to show that it works.

This is a useful post for those wishing to consider alternate auditing methods for monitoring application data but be aware of some of the issues. Trying to put the audit records in the same table can cause complexities. There is also a couple of interesting comment on the Amis blog for this post, the first that suggests that DBMS_WM.ENABLE_VERSION might be an alternative and another commenter suggests that Andres solution is complex.

An interesting post and idea. Holding audit with the data can have good points and bad points. It really depends on the implementation, application, and quantity of data and how often it is amended to make a good qualative decision. Of course Fine Grained Audit is a better solution option in 10g where it now supports update, insert and delete as well as the select statements supported in 9i. Logging and auditing are important aspects of any application design nowadays especially with the regulations that now exist.