Re: Transaction auditing

In article <a4blgk$atn$1_at_usenet.Stanford.EDU>, gail_at_alberich.Stanford.EDU says...
>
>I'm the DBA of a research database which uses Perl DBI and web CGI
>programs for its user interface. The database version is 8.1.7.3
>running on Sun Solaris 2.8. We are currently redesigning the
>underlying table structure and want to include more auditing features.
>We have implemented row level triggers to track updates and deletes.
>This data is stored in separate log tables.
>
>We also have the situation whereby we want to track a "unit of work".
>There are occasions, for example, where a number of updates are
>performed on a number of tables. Individually, a particular row
>update is meaningless. Collectively, all the rows updated in all the
>tables correct an error. We would like to be able to assign a single
>"transaction number" to all rows updated and include that along with
>the row data in the log tables.
>
>A statement level trigger doesn't seem to fit the bill. There are
>going to be a number of SQL statements per "unit of work". I've
>looked into global temporary tables as a way to store data on a
>session basis. Has anyone had experience using global temporary
>tables this way? Does anyone have any other suggestions how I might
>identify and track "transactions"?
>

global temp tables wouldn't help here -- you would need a "commit" trigger to move the data to a persistent store (and there is no such trigger). All of your hard work would just "disappear".

Perhaps you should just label the audited information with a transaction id? See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1354741727492 for that approach. Has a nice tie into LOG MINER as well.

>Thanks for your help.
>--
>Department of Genetics Phone: (650) 498-7145
>School of Medicine Fax: (650) 723-7016
>Stanford University Email: gail_at_genome.stanford.edu
>Stanford, CA 94305-5120 URL: http://genome-www.stanford.edu/

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp