Makrand -- Thanks for the question regarding "how can I tell what sql caused my trigger to fire", version 10g

Submitted on 29-Apr-2008 14:38 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 30-Apr-2008 22:35

You Asked

Hi,

We have a database table from which the rows are deleted and we have no idea how is it happening. Users claim that they are not deleting it through application.
I put an ondelete trigger on that table and stored the rows that were deleted in a table. I want to see the whole SQL which is causing the Delete trigger to be invoked, so that I can know which part of the program is causing the deletion.
Please advise how can I achieve it.

Thanks,
Makrand

and we said...

select sql_text from v$open_cursor where upper(sql_text) like '%DELETE%TABLE_NAME%';

should capture all of the deletes against this table the current session has open.

you can also use find grained auditing to capture any DML against this table.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#sthref2840

Reviews

dbms_utility.format_call_stack April 30, 2008 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: M. Rogel from http://tinyurl.com/4uaw4w

he just could use dbms_utility.format_call_stack in the trigger ?

Followup April 30, 2008 - 8pm Central time zone:

if they wanted to see who called them (probably a program, not even plsql, so it would be "empty")

they want SQL, the actual "delete"

ok April 30, 2008 - 6pm Central time zone

Bookmark | Bottom | Top

Reviewer: M. Rogel from http://tinyurl.com/4uaw4w

he wants to see the sql and not the call stack, i see

Followup April 30, 2008 - 8pm Central time zone:

doh, I should have read ahead :) you caught that yourself.

Alternative April 30, 2008 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from Sydney, Aus

Why not use ORA_SQL_TXT in the trigger ?
v$open_cursor may miss a delete through a view or a synonym, or a delete resulting from a foreign 
key 'on delete cascade'
 
http://download-west.oracle.com/docs/cd/A97630_01/appdev.920/a96590/adg14evt.htm#1005274

Followup April 30, 2008 - 10pm Central time zone:

because ora_sql_txt is only for system event triggers, not dml triggers.

but valid comment on the view/synonym

capture all deletes - %DELETE%

Write a Review