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

08-24-2004, 09:22 AM

gandolf989

Re: Getting the triggering statement inside a trigger

Quote:

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

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.

08-25-2004, 02:16 AM

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.

08-25-2004, 02:21 AM

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.

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.