Get Current SQL in Before delete trigger
Ask_DevNov 25 2010 — edited Nov 25 2010Hi,
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;