4 Replies Latest reply: Aug 19, 2011 2:12 AM by 882850 RSS

    Apex Triggers (auditing)

    882850
      hi

      I am currently in the middle of an application migration to apex where I bumped into the following problem:

      I've a table with a trigger which logs the "who_updated" column after each transaction, when a transaction is performed on the table using an ApEx form, the data is inserted, but, the "who_updated" column contains the user "anonymous" and not the currently logged on user in ApEx.

      the trigger I use very simple/basic:

      CREATE OR REPLACE TRIGGER TR_BU_BI_SYS_USR
      BEFORE UPDATE OR INSERT
      ON SYS_USR
      FOR EACH ROW
      DECLARE
      BEGIN
      :NEW.WHO_UPDATED:=USER;
      END;
      /

      Do you have any idea how i can get the logged in username in my "who_updated" column?

      please advice.

      br.
      waqar
        • 1. Re: Apex Triggers (auditing)
          Udo
          Hi Waqar,

          probably you'd have gotten a faster answer for your question in the forum {forum:id=137}.
          But I think I can help you for the moment:
          USER will give you the name of the current database user. For APEX sessions this is always the technical account used by the web server that hosts APEX. In your case (which is the default in XE) this is the Embedded PL/SQL Gateway (via XDB HTTP Server), and this uses ANONYMOUS.
          So you know why you always get ANONYMOUS instead of an actual APEX user.
          APEX sessions carry this information of the application user in a kind of context variable, APP_USER is the one you are serching for. You can evaluate this information using a function call. Depending on the desired data type, you can use NV or V for (numerical) values. So your code could look like
          CREATE OR REPLACE TRIGGER TR_BU_BI_SYS_USR
          BEFORE UPDATE OR INSERT
          ON SYS_USR
          FOR EACH ROW
          DECLARE
          BEGIN
          :NEW.WHO_UPDATED:=V ('APP_USER');
          END;
          /
          -Udo
          • 2. Re: Apex Triggers (auditing)
            882850
            hi Udo

            that did the trick

            many thanks for the hints..
            br
            Waqar
            • 3. Re: Apex Triggers (auditing)
              882850
              hi Udo

              that did the trick

              many thanks for the hints..
              br
              Waqar
              • 4. Re: Apex Triggers (auditing)
                882850
                hi Udo

                that did the trick

                many thanks for the hints..
                br
                Waqar