title: How to describe user-input values in trigger - Oracle PL/SQL
body: <p>I am learning PL/SQL and have attempted to create a trigger where once a particular condition has been met in the fact table, data will be routed to the appropriate dimension table. </p>
  
  <p>There is a fact table<br>
  BHM_FACT_TABLE</p>
  
  <p>Four dimension tables     </p>
  
  <ul>
  <li>BHM_EMPLOYEES  </li>
  <li>BHM_ACCOUNTS</li>
  <li>BHM_SUBSCRIBERS</li>
  <li>BHM_EXPENSES</li>
  </ul>
  
  <p>In the fact table, there is an "S_Number" column that serves as the primary key for the fact table. There is also a "Code" column that lists either A, B, C, or D, depending on whether the record lists an employee, an account, a subscriber, or an expense (this is a very basic, rudimentary table, by the way). </p>
  
  <p>The fact table only contains the following columns: </p>
  
  <ul>
  <li>S_Number</li>
  <li>Code</li>
  <li>Employees</li>
  <li>Accounts</li>
  <li>Subscribers</li>
  <li>Expenses. </li>
  </ul>
  
  <p>The dimension tables contain additional columns to hold data about the employees, accounts, subscribers, and expenses (you can see the columns in details in the trigger below). </p>
  
  <p>What I want the trigger to do is route the data that the user inputs to the appropriate dimension table, based on what is entered in the fact table - i.e., once a row is entered with a code of 'A', which means that this record is of an employee - then that will prompt the rest of the values input to go to the BHM_EMPLOYEES table. And so on and so forth. So, this is what I came up with: </p>
  
  <pre><code>CREATE OR replace TRIGGER bhm_test
    AFTER INSERT ON bhm_fact_table
    FOR EACH ROW
  DECLARE
      v_code VARCHAR2 (1);
  BEGIN
      SELECT code
      INTO   v_code
      FROM   bhm_fact_table;
  
      IF v_code = 'A' THEN
        INSERT INTO bhm_employees
                    (s_number,
                     code,
                     employeename,
                     employeeaddress,
                     employeessn,
                     employeephonenumber,
                     employeesalary)
        VALUES      ( :NEW.s_number,
                     :NEW.code,
                     employeename,
                     employeeaddress,
                     employeessn,
                     employeephonenumber,
                     employeesalary );
      ELSIF v_code = 'B' THEN
        INSERT INTO bhm_accounts
                    (s_number,
                     code,
                     accountname,
                     address,
                     taxid,
                     phonenumber,
                     invoicetotal)
        VALUES      ( :NEW.s_number,
                     :NEW.code,
                     accountname,
                     address,
                     taxid,
                     phonenumber,
                     invoicetotal );
      ELSIF v_code = 'C' THEN
        INSERT INTO bhm_subscribers
                    (s_number,
                     code,
                     subscribername,
                     subscriberaddress,
                     subscriberphone,
                     subscribertype,
                     subscriberpaid)
        VALUES      ( :NEW.s_number,
                     :NEW.code,
                     subscribername,
                     subscriberaddress,
                     subscriberphone,
                     subscribertype,
                     subscriberpaid );
      ELSIF v_code = 'D' THEN
        INSERT INTO bhm_expenses
                    (s_number,
                     code,
                     expensesname,
                     expensesamt)
        VALUES      ( :NEW.s_number,
                     :NEW.code,
                     expensesname,
                     expensesamt );
      END IF;
  END; 
  </code></pre>
  
  <p>For this I am getting the error 
  ORA-00984: Column Not Allowed Here </p>
  
  <p>for each last column listed for VALUES (employeesalary, invoicetotal, subscriberpaid, and expensesamt). I took away the bind variables for the columns that you see without them; I originally had them by all of the values, but got the "Bad Bind Variables" error. </p>
  
  <p>So my first question is, what would I put in the VALUES clause, if I don't know that information yet and it won't be stored (and thus available to reference) from the fact table?</p>
  
  <p>My second question is - am I even going remotely in the right direction with this trigger?</p>