3 Replies Latest reply: Oct 8, 2012 4:45 PM by rp0428 RSS

    Help with Oracle Database XE edition tutorial

    966732
      Hi,

      I am a student and our instructor has told us to install 11g and now has us running through the 10g tutorial and I've run into a number of problems. There is one issue that I cannot find the solution for and was hoping that somebody could give me a hand.

      The tutorial has us make a table. This works...

      CREATE TABLE audit_record_tb1
      (
      user_value varchar2(25),
      date_recorded timestamp(6)
      );


      Later on, we have to create a trigger that will write to the table when another table's values are updated. This one doesn't work. It gives an error stating 'too many values'

      INSERT INTO audit_record_tb1
      VALUES(SYS_CONTEXT('userenv', 'current_user'),
      sysdate, :old.employee_id, :old.salary, :new.salary);
      EXCEPTION
      WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-10001, 'audit trigger failure');

      There has been no instruction to add columns to the audit_record_tb1 table and I am at a loss as to what the issue is at this point.

      Thanks in advance for your help.
        • 1. Re: Help with Oracle Database XE edition tutorial
          Billy~Verreynne
          The ALTER TABLE DDL command is used to change a table definition (adding/deleting columns for example).

          See Oracle® Database SQL Language Reference for details.
          • 2. Re: Help with Oracle Database XE edition tutorial
            Sven W.
            963729 wrote:
            There has been no instruction to add columns to the audit_record_tb1 table and I am at a loss as to what the issue is at this point.
            Maybe the tutorial doesn't want you to insert 5 values into a 2 column table? Ok what is the tables task and what attributes are there?

            It is named: audit_record_tb1. So the purpose is probably to track everything that was changed in some other table. The columns (=attributes) are: user_value and date_recorded.
            Date_recorded clear is the data when some action took place (=sysdate). User_value could be the old value from some column or it could be the user who did the task.

            As you noticed there might be some information mission. Like which column does the old value come from etc.
            Of cause you could insert all this into the user_value column. Unfortunatly that is no a very large column (25 char).

            You solution to add new columns is a good idea. The oracle documentation has the syntax diagrams to help you do this. It is a god idea to bookmark the documentation.
            Homepage 11g: http://www.oracle.com/pls/db112/homepage
            And the SQL Language reference: http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm

            Another solution could be to insert each old value into a separate row.

            Something like
            INSERT INTO audit_record_tb1 
            VALUES(to_char(:old.employee_id), sysdate);
            
            INSERT INTO audit_record_tb1 
            VALUES(to_char(:old.salary), sysdate);
            ...
            It is not needed to insert the new value. Why? because this value is still in the current record. And if that record is changed again the trigger will write another entry to the audit table.

            Edited by: Sven W. on Oct 8, 2012 9:16 PM
            • 3. Re: Help with Oracle Database XE edition tutorial
              rp0428
              Welcome to the forum!
              >
              Later on, we have to create a trigger that will write to the table when another table's values are updated. This one doesn't work. It gives an error stating 'too many values'

              INSERT INTO audit_record_tb1
              VALUES(SYS_CONTEXT('userenv', 'current_user'),
              sysdate, :old.employee_id, :old.salary, :new.salary);
              EXCEPTION
              WHEN OTHERS THEN
              RAISE_APPLICATION_ERROR(-10001, 'audit trigger failure');

              There has been no instruction to add columns to the audit_record_tb1 table and I am at a loss as to what the issue is at this point.
              >
              You can't put 5 items into a table that has two columns.

              What does 'user_value' in the audit_record_tb1 table represent? If it represents the old value (when a value is being changed) then insert three records: one for the user making the change, one for the old employee_id vaule and one for the old salary value.

              Why are you using the SYS_CONTEXT value? Was there some instruction to do that?