5 Replies Latest reply: Nov 25, 2010 10:13 AM by Sven W. RSS

    Get Current SQL in Before delete trigger

    486778
      Hi,
      I have created a before delete trigger to track which records are deleted from a table I need to know what statements fires the trigger. can someone please describe how I can retrieve the current SQL.

      using sys_context('userenv','CURRENT_SQL') returns null for CURRENT SQL

      Note:
      For me the easier is to enable auditing and audit delete on the table however at the moment I cant get a downtime from the business to bounce the database to enable auditing.



      CREATE OR REPLACE TRIGGER before_delete BEFORE DELETE
      ON AUDIT_TEST_TAB
      FOR EACH ROW

      DECLARE
      v_username varchar2(50);
      v_stmt varchar2(255);
      v_client_info varchar2(200);
      v_os_user varchar2(50);
      v_machine varchar2(50);
      v_program varchar2(50);
      v_module varchar2(50);
      v_auth_type varchar2(200);
      v_ip_addr varchar2(200);
      v_sql_statement VARCHAR2(4000);



      BEGIN

      SELECT sys_context ('USERENV', 'CURRENT_SQL')
      INTO v_sql_statement
      FROM dual;





      -----------insert into logging table statment ----





      --------------
      end;
        • 1. Re: Get Current SQL in Before delete trigger
          Solomon Yakobson
          Samuel K wrote:

          using sys_context('userenv','CURRENT_SQL') returns null for CURRENT SQL
          RTFM sys_context:

          CURRENT_SQL returns the first 4K bytes of the current SQL <font size=3 color=red>that triggered the fine-grained auditing event.</font>
          You get NULL since in your case there was no fine-grained auditing event

          SY.
          • 2. Re: Get Current SQL in Before delete trigger
            486778
            can you please elaborate more on FGA event and how I should use it.
            • 3. Re: Get Current SQL in Before delete trigger
              Dbb
              You need enable audit to use FGA
              • 4. Re: Get Current SQL in Before delete trigger
                Solomon Yakobson
                Samuel K wrote:
                can you please elaborate more on FGA event and how I should use it.
                Well, FGA allows to add certain conditions to querying certain tables. So if, for example, user issues select againt employee table additional WHERE clause condition JOB != 'MANAGER' is automatically added. Then sys_context('userenv','CURRENT_SQL') would return SQL statement without that FGA added additional condition. So I don't believe this can help you. And why do you need triggering statement to begin with?

                SY.
                • 5. Re: Get Current SQL in Before delete trigger
                  Sven W.
                  A few comments.

                  Lets assume you run a delete statement that deletes 550 rows from your table. If your trigger is working then the very same statement would be stored 550 times. I think an BEFORE or better an AFTER delete STATEMENT level trigger would be the better choice. Why AFTER? Because if the delete operation fails, for example because of existing child records, then everything is rolled back anyway to the implicit savepoint just before the delete.

                  So to store the SQL statement the correct trigger would be an AFTER STATEMENT DELETE trigger.

                  Now what to store: You want the sql statement. You could try to find the cursor/currently running SQL. It might be tricky to separate that from the SQLs that you run to find this out.

                  It could even be possible to simply save all commands that are in your PGA/Cached cursor area. First find out yur session, then store the SQL_text (first 60 chars) for all the cursors in this session by using v$open_cursor or the first 1000 chars by using v$sql.

                  Here are a few views that might be helpful. v$session , v$open_cursor, v$sql, v$sqltext, v$sql_bind_data, v$sql_bind_capture, dba_hist_sqltext