3 Replies Latest reply: Oct 15, 2012 3:52 AM by Bao Tian RSS

    Audit Problem APEX 4.2 - Can't create new line in table

    Bao Tian
      Hello I've got problems with the trigger. The audit trigger works fine but I can't create new lines in my table (table form), I'll get an error.
      Only if I use the standard trigger set up with :NEW."ID" is null then select "STANDORT_SEQ".nextval into :NEW."ID" from sys.dual;

      Because I don't want that the ID will be put on the form. ID is my primary key.
      Do I have to use two triggers together, the one for automatic new ID and the audit trigger?
      I tried to put both triggers together but it doesn't work.

      create or replace TRIGGER "bi_TEST"
      before insert on "TEST"
      for each row
      begin
      IF (inserting) THEN
      BEGIN
      :new.Last_updated_by := nvl(v('APP_USER'), 'user');
      :new.last_updated_on := systimestamp;
      END;
      ELSIF (updating) THEN
      BEGIN
      :NEW.Last_updated_by := nvl(v('APP_USER'), 'user');
      :NEW.last_updated_on := systimestamp;
      END;
      ELSIF
      :NEW."ID" is null then select "TEST_SEQ".nextval into :NEW."ID" from sys.dual;
      END IF;
      END;
        • 1. Re: Audit Problem APEX 4.2 - Can't create new line in table
          Prabodh
          Hi *963197*,
          Welcome to Oracle Forums!
          Please acquaint yourself with the FAQ and forum etiquette if you haven't already done so.

          Always state
          <ul>
          <li>Apex Version</li>
          <li>DB Version and edition</li>
          <li>Web server used.I.e. EPG, OHS, ApexListner Standalone or with J2EE container</li>
          <li>When asking about forms always state tabular form if it is a tabular form</li>
          <li>When asking about reports always state Classic / IR</li>
          <li>Always post code snippets enclosed in a pair of &#123;code&#125; tags as explained in FAQ</li>
          </ul>
          Hello I've got problems with the trigger. The audit trigger works fine but I can't create new lines in my table (table form), I'll get an error.
          What error do you get?
          >
          Only if I use the standard trigger set up with :NEW."ID" is null then select "STANDORT_SEQ".nextval into :NEW."ID" from sys.dual;

          Because I don't want that the ID will be put on the form. ID is my primary key.
          Do I have to use two triggers together, the one for automatic new ID and the audit trigger?
          I tried to put both triggers together but it doesn't work.

          create or replace TRIGGER "bi_TEST"
          before insert on "TEST"
          for each row
          begin
          IF (inserting) THEN
          BEGIN
          :new.Last_updated_by := nvl(v('APP_USER'), 'user');
          :new.last_updated_on := systimestamp;
          END;
          ELSIF (updating) THEN
          BEGIN
          :NEW.Last_updated_by := nvl(v('APP_USER'), 'user');
          :NEW.last_updated_on := systimestamp;
          END;
          ELSIF
          :NEW."ID" is null then select "TEST_SEQ".nextval into :NEW."ID" from sys.dual;
          END IF;
          END;>
          Your indiscriminate use of double quotes is scary. Double quotes make the column/object name case sensitive.
          I.e. "biu_TEST" != BIU_TEST != biu_test....
          Try
          create or replace TRIGGER biu_TEST
            before insert OR UPDATE on "TEST"
            for each row
           begin
              IF  :NEW."ID" is null then --Can be true only for Insert, not for Update
                  select "TEST_SEQ".nextval into :NEW."ID" from sys.dual;
              END IF;
              :new.Last_updated_by := nvl(v('APP_USER'), USER); -- No quotes !
              :new.last_updated_on := systimestamp; -- Are you sure your column is Timestamp? 
              IF INSERTING THEN
                INSERT INTO <AUDIT_TABLE>(.....)
                VALUES (....);
              END IF;
          END;
          Cheers,
          • 2. Re: Audit Problem APEX 4.2 - Can't create new line in table
            Patrick Wolf-Oracle
            Hi,

            you haven't told us which error you got.

            BTW, I assume the following line
            :new.Last_updated_by := nvl(v('APP_USER'), 'user');
            should actually be
            :new.Last_updated_by := nvl(v('APP_USER'), user);
            Regards
            Patrick
            -----------
            My Blog: http://www.inside-oracle-apex.com
            APEX Plug-Ins: http://apex.oracle.com/plugins
            Twitter: http://www.twitter.com/patrickwolf
            • 3. Re: Audit Problem APEX 4.2 - Can't create new line in table
              Bao Tian
              Ok it works, I created two triggers.