6 Replies Latest reply: Dec 12, 2012 3:51 AM by AquaNX4 RSS

    Table Level Audit Trigger

    AquaNX4
      For the life of me, I can't figure out why this isn't working:
      SQL Developer Version 3.2.20.09


      CREATE OR REPLACE
      trigger TRG_PROP_AUDIT

      AFTER insert or update or delete on PROP_BOOK_SI
      for each row

      begin
      IF INSERTING THEN
      INSERT INTO PROPERTY_AUDIT
      (AUDIT_DATE, ENTERED_BY, OPERATION)
      VALUES
      (AUDIT_DATE, ENTERED_BY, 'INSERT');

      ELSIF updating then
      INSERT INTO PROPERTY_AUDIT
      (AUDIT_DATE, ENTERED_BY, OPERATION)
      VALUES
      (AUDIT_DATE, ENTERED_BY, 'UPDATE');

      ELSIF deleting then
      INSERT INTO PROPERTY_AUDIT
      (AUDIT_DATE, ENTERED_BY, OPERATION)
      VALUES
      (AUDIT_DATE, ENTERED_BY, 'DELETE');


      end if;
      end TRG_PROP_AUDIT;

      I'm getting this error:
      Error(3,1): PL/SQL: SQL Statement ignored
      Error(6,22): PL/SQL: ORA-00984: column not allowed here
      Error(9,1): PL/SQL: SQL Statement ignored
      Error(12,22): PL/SQL: ORA-00984: column not allowed here
      Error(15,1): PL/SQL: SQL Statement ignored


      What am I doing wrong? I've researched this extensively on the web, and supposedly you are not allowed to enter the column names in the [VALUES] expression. So how am I supposed to put that information in the appropriate columns? I'm so confused right now. I would appreciate any help...
        • 1. Re: Table Level Audit Trigger
          evrm
          Hi,

          I am not sure this is the right forum for your message.
          Next time post it in the [url https://forums.oracle.com/forums/forum.jspa?forumID=75]SQL and PL/SQL forum

          But anyway you can't use column name directly in a trigger.
          You need to use :new or :old as a prefix like so:
          CREATE OR REPLACE 
          trigger TRG_PROP_AUDIT
          
          AFTER insert or update or delete on PROP_BOOK_SI
          for each row
          
          begin
          IF INSERTING THEN
          INSERT INTO PROPERTY_AUDIT
          (AUDIT_DATE, ENTERED_BY, OPERATION)
          VALUES 
          (:new.AUDIT_DATE, :new.ENTERED_BY, 'INSERT');
          
          ELSIF updating then
          INSERT INTO PROPERTY_AUDIT
          (AUDIT_DATE, ENTERED_BY, OPERATION)
          VALUES 
          (:new.AUDIT_DATE, :new.ENTERED_BY, 'UPDATE');
          
          ELSIF deleting then 
          INSERT INTO PROPERTY_AUDIT
          (AUDIT_DATE, ENTERED_BY, OPERATION)
          VALUES 
          (:old.AUDIT_DATE, :old.ENTERED_BY, 'DELETE');
          end if;
          end TRG_PROP_AUDIT;
          regards,
          Erik-jan
          • 2. Re: Table Level Audit Trigger
            AquaNX4
            That worked! Thank you very much!

            How do I get the [app_user] data into the audit table? Thanks for your help!
            • 3. Re: Table Level Audit Trigger
              evrm
              Hi,

              >
              How do I get the [app_user] data into the audit table? Thanks for your help!
              >

              In the trigger code you can use the v function to get the value of APP_USER.
              v('APP_USER')
              regards,
              Erik-jan
              • 4. Re: Table Level Audit Trigger
                AquaNX4
                I think I tried that, but it came back with some error about [not being allowed to use virtual columns]. Ever hear of that error?
                • 5. Re: Table Level Audit Trigger
                  chrdei
                  Using the v('APP_USER') works. I am using this in triggers as well.
                  Do not forget to fallback to the database user if the APP_USER is empty! Maybe you will insert data from time to time in the corresponding tables without a valid APEX session.
                  nvl( v('APP_USER'), user );
                  Edited by: chrdei on 11.12.2012 09:40
                  • 6. Re: Table Level Audit Trigger
                    AquaNX4
                    Thanks a lot. Works like a charm....