Transaction auditing

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"?

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/