title: Oracle trigger conflict with null
body: <p>First and foremost, this is for an assignment, so most of the really cool pre-written functions you'll want to suggest, I will not be allowed to use.</p>
  
  <p>I have a couple tables that all have fields</p>
  
  <pre><code>creation_date
  created_by
  last_update_date
  last_updated_by
  </code></pre>
  
  <p>I need to write a trigger that fills these in for creation or updating. The problem is, these tables have null values that are problematic to me. Example:</p>
  
  <pre><code>CREATE TABLE parts
  (
  pno                NUMBER,
  pname              VARCHAR2(50) NOT NULL,
  qoh                NUMBER       NOT NULL,
  price              NUMBER(5,2),
  reorder_level      NUMBER(2),
  creation_date      DATE         NOT NULL,
  created_by         VARCHAR2(10) NOT NULL,
  last_update_date   DATE         NOT NULL,
  last_updated_by    VARCHAR2(10) NOT NULL,
  CONSTRAINT parts_PK PRIMARY KEY (pno))
  </code></pre>
  
  <p>These NOT NULL's are given to me and I'm not allowed to change them. So I'm having trouble conceptualizing this.</p>
  
  <ul>
  <li>If my trigger adds these values before the field is created, I can't do an INSERT INTO with those fields blank because they're NOT NULL.  </li>
  <li>If my trigger adds these values after the field is created, I get compilation errors ORA-00903 and 00922. Invalid table name and invalid option.</li>
  </ul>
  
  <p>I was thinking my trigger would look like</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER pcreate
    BEFORE UPDATE on parts
    FOR EACH ROW
  BEGIN
    UPDATE
      SET creation_date = SYSDATE;
      SET created_by = USER;
      SET last_update_date = SYSDATE;
      SET last_updated_by = USER;
  END;
  /
  
  CREATE OR REPLACE TRIGGER pchange
    BEFORE UPDATE on parts
    FOR EACH ROW
  BEGIN
    UPDATE
      SET last_update_date = SYSDATE;
      SET last_updated_by = USER;
  END;
  /
  repeat for the other tables
  </code></pre>
  
  <p>I might be allowed to use UPSERT but I don't really know how that works.. Any suggestions are welcome. I'm really in this to learn so any other advice is appreciated.</p>
  
  <p>EDIT:</p>
  
  <p>My package that will not acknowledge the trigger is as follows. Do I need to call the trigger inside the package?</p>
  
  <pre><code>CREATE OR REPLACE PACKAGE process_orders 
  AS
    PROCEDURE add_order (p_cno IN NUMBER, p_eno IN NUMBER, p_received IN DATE);
    PROCEDURE add_order_details (p_ono IN NUMBER, p_pno IN NUMBER, p_qty IN NUMBER);
    PROCEDURE ship_order (p_ono IN NUMBER, p_sdate IN DATE);
    PROCEDURE delete_order (p_ono IN NUMBER);
    FUNCTION total_emp_sales (f_eno IN NUMBER) RETURN NUMBER;
  END process_orders;
  /
  
  CREATE OR REPLACE PACKAGE BODY process_orders 
  AS
    PROCEDURE add_order (p_cno IN NUMBER, p_eno IN NUMBER, p_received IN DATE) 
    IS
    ao_emsg       VARCHAR2(100);
    p_rec_today   DATE;
    BEGIN
      SELECT SYSDATE INTO p_rec_today FROM dual;
      IF p_received is null THEN
          INSERT INTO orders (ono, cno, eno, received)
          VALUES(order_number_seq.NEXTVAL,p_cno,p_eno,p_rec_today);
      ELSE
          INSERT INTO orders (ono, cno, eno, received)
      VALUES(order_number_seq.NEXTVAL,p_cno,p_eno,p_received);
      END IF;
  
    EXCEPTION
      WHEN OTHERS THEN
        ao_emsg := substr(SQLERRM,1,100);
        INSERT INTO orders_errors (ono,transaction_date,message)
        VALUES(order_number_seq.CURRVAL,SYSDATE,ao_emsg);
    END;
    --Etc. Procedures
  END;
  /
  </code></pre>