This content has been marked as final. Show 5 replies
Samuel K wrote:RTFM sys_context:
using sys_context('userenv','CURRENT_SQL') returns null for CURRENT SQL
CURRENT_SQL returns the first 4K bytes of the current SQL <font size=3 color=red>that triggered the fine-grained auditing event.</font>
You get NULL since in your case there was no fine-grained auditing event
Samuel K wrote:Well, FGA allows to add certain conditions to querying certain tables. So if, for example, user issues select againt employee table additional WHERE clause condition JOB != 'MANAGER' is automatically added. Then sys_context('userenv','CURRENT_SQL') would return SQL statement without that FGA added additional condition. So I don't believe this can help you. And why do you need triggering statement to begin with?
can you please elaborate more on FGA event and how I should use it.
A few comments.
Lets assume you run a delete statement that deletes 550 rows from your table. If your trigger is working then the very same statement would be stored 550 times. I think an BEFORE or better an AFTER delete STATEMENT level trigger would be the better choice. Why AFTER? Because if the delete operation fails, for example because of existing child records, then everything is rolled back anyway to the implicit savepoint just before the delete.
So to store the SQL statement the correct trigger would be an AFTER STATEMENT DELETE trigger.
Now what to store: You want the sql statement. You could try to find the cursor/currently running SQL. It might be tricky to separate that from the SQLs that you run to find this out.
It could even be possible to simply save all commands that are in your PGA/Cached cursor area. First find out yur session, then store the SQL_text (first 60 chars) for all the cursors in this session by using v$open_cursor or the first 1000 chars by using v$sql.
Here are a few views that might be helpful. v$session , v$open_cursor, v$sql, v$sqltext, v$sql_bind_data, v$sql_bind_capture, dba_hist_sqltext