Trigger to capture the firing SQL

I want to capture the SQL in case someone deletes rows from a table. Oracle audit does not provide this in 8i. It only tells you the type of SQL (i.e. insert, update, delete..etc) not the content of the SQL.

So I created a trigger trying to capture this. My key trigger statement looks like this:

select sql_text into v_sql_text from v$sql where hash_value =
(select prev_hash_value from v$session where sid =
(select distinct sid from v$mystat));

v_sql_Text is a variable, after this selection, I insert it into my audit table.

This works fine, except the SQL I capture is the trigger SQL itself, instead of the firing SQL - although I used prev_hash_value instead of sql_hash_value. I tried both BEFORE and AFTER in the trigger condition, it makes no difference.

I understand if I use sql_hash_value, it would be the current, which is the trigger SQL itself, SQL. But I can not comprehend why it does not make difference even though I use prev_hash_value which in my mind should be the previous SQL and it should be the firing SQL.

So this boils down to 2 short question:

1. Why does prev_hash_value and sql_hash_value make no difference in v$session ?
2. Is there a way to capture a firing SQL ?

I would appreciate any input on this. Received on Thu Apr 04 2002 - 14:47:42 CST