Getting the triggering statement inside a trigger

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 ???

Quote:

---

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.

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.

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.

Code:

---

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>

---

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.