triggers and finding calling parent

We have a weblogic application which runs on top of oracle database (8.1.7 on Solaris to be more exact). 1 of the tables looses some records, usually overnight, and I need to find out why.

This is a rather large application that got inherited from a different software company, and most likely there is a process, stored procedure, script, job, whatever that's causing some havoc. The chance that someone manually is deleting these records is very low.

I'm thinking of creating a "for each row" trigger that would log any activity on this table. Through the trigger I can store the timestamp and column values, but can I access what called the statement?

If there is a stored procedure, oracle job, or even a sql+ console, that calls a query, does oracle know this? In other words, would the trigger know where the statement was coming from? And if yes, how can I access this information? Even a process ID, anything would be helpful.

Any other ideas on how a change to a table can be tracked? Received on Mon Mar 10 2003 - 17:47:13 CST