Getting the triggering statement inside a trigger

[ramesh_1600]

Is there a way to get the SQL statement which fired the trigger, inside the trigger block ?

Say, I issue the following statement,
-----------------------------------------------
SQL> update scott.emp set sal = 0 ; -- Statement which fires trigger
SQL> commit;

Which fires the the following trigger,
----------------------------------------------
create or replace trigger scott.test_trigger
before update on scott.emp
begin
/*Code to store the SQL statement which fired the trigger */
insert into EMP_UPDATE_SQLS values ( **TRIGGERING_SQL_STMT** );
end;

Result expected:
---------------------
A row in EMP_UPDATE_SQLS , with the sql statement that fired the trigger, ( a row with the value 'update scott.emp set sal = 0' ).


Is it possible ???

[gandolf989]

Originally posted by ramesh_1600
Is there a way to get the SQL statement which fired the trigger, inside the trigger block ?

Say, I issue the following statement,
-----------------------------------------------
SQL> update scott.emp set sal = 0 ; -- Statement which fires trigger
SQL> commit;

Which fires the the following trigger,
----------------------------------------------
create or replace trigger scott.test_trigger
before update on scott.emp
begin
/*Code to store the SQL statement which fired the trigger */
insert into EMP_UPDATE_SQLS values ( **TRIGGERING_SQL_STMT** );
end;

Result expected:
---------------------
A row in EMP_UPDATE_SQLS , with the sql statement that fired the trigger, ( a row with the value 'update scott.emp set sal = 0' ).


Is it possible ???

Yes.

If you compare the fields between the :OLD and :NEW meta rows. the columns that are different are the ones that changed, assuming an update statement.

[surajitmitra78]

friends,

he is asking for the DML statement, not the identifiers of fetching the OLD and the NEW value.

ramesh correct me if i am wrong.

[oracle_faq]

Looks like you are trying to acheive the Oracle provided LOGMINER utility.

I would recommend looking into LOGMINER, to get all the statements that were issued against a particular table.

[padders]

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> CREATE OR REPLACE FUNCTION cur_sql_txt
  2    RETURN CLOB
  3  AS
  4    v_cnt BINARY_INTEGER;
  5    v_sql ORA_NAME_LIST_T;
  6    v_rtn CLOB;
  7  BEGIN
  8    v_cnt := ora_sql_txt (v_sql);
  9    FOR l_bit IN 1..v_cnt LOOP
 10      v_rtn := v_rtn || RTRIM (v_sql (l_bit), CHR (0));
 11    END LOOP;
 12    RETURN RTRIM (v_rtn, CHR (10)) || ';';
 13  END;
 14  /

Function created.

SQL> CREATE OR REPLACE TRIGGER trigger_name
  2    BEFORE UPDATE ON emp
  3    FOR EACH ROW
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE (cur_sql_txt);
  6  END;
  7  /

Trigger created.

SQL> SET SERVEROUTPUT ON;
SQL> UPDATE emp
  2  SET    empno = empno,
  3         ename = ename
  4  WHERE  ROWNUM = 1;
UPDATE emp
SET    empno = empno,
       ename = ename
WHERE  ROWNUM =
:"SYS_B_0";

1 row updated.

SQL>

[ramesh_1600]

Thanks for all your posts.

Settled with solution which is similar to the one that Padders had posted. I was trying to use the v$sqlarea, to get the current sql statement ( most probably the statement which fires the trigger ), but Padders' solution was smarter.

Thanks all.

regards,
Ramesh.