Getting the triggering statement inside a trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Getting the triggering statement inside a trigger

  1. #1
    Join Date
    May 2003
    Posts
    12

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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,966

    Re: Getting the triggering statement inside a trigger

    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.

  3. #3
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    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.
    Regards,
    Surajit K Mitra

  4. #4
    Join Date
    Feb 2004
    Posts
    77
    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.

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    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>

  6. #6
    Join Date
    May 2003
    Posts
    12
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width