CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
user3216533
user3216533

Reputation: 65

Get statement which fire trigger in Oracle

I tried to get the statement which fire my trigger, and i found anything.... There was a tip in oracle 8i, but that doesn't work now.

I am on Oracle 10 and 11g.

I tried with BEFORE or AFTER trigger, and with SQL_ID or PREV_SQL_ID in v$session with this query :

 select substr(e.program,1,20),sql_text into l_program, sql_text 
 from v$sql s, v$session e
  where s.sql_id = e.sql_id
  and e.status = 'ACTIVE'
    and e.sid = sys_context('USERENV','SID')
    and rownum = 1;

Either i got current statement (in v$session), either the statement before my update query which fire the trigger....

I tried compound trigger, too, but same... Because i thought, if i tried to get the statement before or after statement, it will work... Nothing....

Someone have an idea ? I don't believe there is no solution...

Upvotes: 2

Views: 559

Answers (1)

Gerrat
Gerrat

Reputation: 29690

We have triggers like this, to capture the sql, that worked for years, but became unreliable in oracle 10, and stopped working outright in Oracle 11.

FYI: Compound triggers aren't available in Oracle 10 (but won't help anyway).

The only reliable way to capture the sql statement now is through Standard Auditing.

You'd have to select the types of operations you want to audit, as well as applying it to all tables you want to capture the sql against.

Upvotes: 0

Related Questions