Re: View SQL in trigger

From original note >>>
Is there a way to grab the SQL statement that is being processed in a trigger? In other words if I do a "update foo set thing = "dog" where type = "animal" and the update trigger fires on foo, can I somehow capture the SQL statement that caused the trigger to fire? <<<

Since you are talking after the fact, that is, after the trigger has already fired, and I believe that you mean without making code modifications, the answer is NO in native Oracle.

You can use the v$session, v$process, and v$sqlarea dynamic performance tables to view the SQL for a running process without having to modify the code.

If you really want to see what SQL is involved from start to finish you will need to buy and test one of the redo log sniffers available on the market like SQLTRAX. Products like this can reconstruct transactions after the fact from the log entries.

Trying to Oracle SQL tracing and tkprof might do the trick if are willing to accept the results from a job you know will cause the trigger to fire.

Hope this helps.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Thu Dec 05 1996 - 00:00:00 CST