Sniff out the actual request that has activated a trigger

Question

I'm facing a problem in my work that has troubled the services for a couple weeks. I have written a trigger to try to catch the origin or a bug. but it's like searching a needle in a haystack.

Is there any way to include the DML transaction in a var so that i can see the actual transaction that is activating the trigger ?

Like imagine :

CREATE OR REPLACE TRIGGER SYSADM.WOW_TRIGGER
AFTER UPDATE
ON SYSADM.TABLE1 REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.COLUMN1 is null and new.COLUMN2 is not null and NEW.COLUMN in ('A','B')
      )
DECLARE
[...]
V_catch clob;

begin 

[...]
end;

===> how do i register inside the V_catch this transaction: update table1 set column2 = "WHATEVER" where COLUMN15555 = 1 that happened following a customer's click in the software ?

i tried by doing some nasty jobs on it by writting custom if condition on every columns to try to get the changed values :

if :NEW.column1 is not null then 
v_catch := v_catch || :NEW.column1;
end if;
if :NEW.column2 is not null then 
v_catch := v_catch || :NEW.column2;
end if;

[...]

That literally does not work as intended often times as we got notified of the bug, and in the report, v_catch was returning non valuables values, and the bug could still not be located.

I'm pretty sure there could be a method out there to do that, but i'm still a beginners and self learning in PLSQL and SQL for oracle. Maybe i overlooked something, maybe this is the only method existing. can any veteran help me out on this pls?

Answer 1

Auditing is definitely the right answer. But given that you have a constraint against using auditing, triggers are your next best option.

The trouble is, outside of DDL/system-event triggers or fine-grain auditing (which can use original_sql_txt and SYS_CONTEXT('USERENV','CURRENT_SQL'), respectively), I don't think it's possible to get the triggering SQL in a DML trigger in a clean manner. While you'd think you could query v$session.sql_id, the query to do so will replace it with itself. However, here's a hack that'll get close, by looking at what cursors are open by the session and were active in the past second.

create table tmp55 (col1 integer);

create or replace trigger tr_tmp55 after insert or update or delete on tmp55 for each row
begin
  FOR rec_cursor IN (SELECT oc.*,s.sql_fulltext
                       FROM v$open_cursor oc,
                            v$sql s
                      WHERE oc.sid = SYS_CONTEXT('USERENV','SID')
                        AND oc.child_address = s.child_address
                        AND oc.sql_id = s.sql_id
                        AND s.last_active_time >= SYSDATE - 1/86400
                        AND UPPER(sql_fulltext) LIKE '%TMP55%'
                        AND UPPER(sql_fulltext) NOT LIKE '%OPEN_CURSOR%')
  LOOP
    dbms_output.put_line(SUBSTR(rec_cursor.sql_fulltext,1,32676));
  END LOOP; 
end;

It could return more than one row, only one of which is the correct SQL. Also to weed down results an assumption is made that the DML will name the table (rather than through a view or synonym). You'll want to change the dbms_output to insert into a log table along with helpful identifying session info. Obviously this is rough and can be tweaked, but it gives the basic idea.