Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get Current SQL in Before delete trigger

Ask_DevNov 25 2010 — edited Nov 25 2010
Hi,
I have created a before delete trigger to track which records are deleted from a table I need to know what statements fires the trigger. can someone please describe how I can retrieve the current SQL.

using sys_context('userenv','CURRENT_SQL') returns null for CURRENT SQL

Note:
For me the easier is to enable auditing and audit delete on the table however at the moment I cant get a downtime from the business to bounce the database to enable auditing.



CREATE OR REPLACE TRIGGER before_delete BEFORE DELETE
ON AUDIT_TEST_TAB
FOR EACH ROW

DECLARE
v_username varchar2(50);
v_stmt varchar2(255);
v_client_info varchar2(200);
v_os_user varchar2(50);
v_machine varchar2(50);
v_program varchar2(50);
v_module varchar2(50);
v_auth_type varchar2(200);
v_ip_addr varchar2(200);
v_sql_statement VARCHAR2(4000);



BEGIN

SELECT sys_context ('USERENV', 'CURRENT_SQL')
INTO v_sql_statement
FROM dual;





-----------insert into logging table statment ----





--------------
end;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 23 2010
Added on Nov 25 2010
5 comments
2,038 views