title: Comparing two dates with an insert trigger
body: <p>I want to make sure that the suborder date is not before the orderdate using this trigger:::</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER suborder_date before
    INSERT ON suborder FOR EACH row DECLARE suborder_dd DATE;
    orderdate DATE;
    BEGIN
      SELECT reqshipdate INTO suborder_dd FROM suborder;
      SELECT orders.orddate INTO orderdate FROM orders;
      IF orderdate &lt; suborder_dd THEN
        raise_application_error(-20002,('Required Ship Date is before Order Date'));
      END IF;
    END;
  </code></pre>
  
  <p>This compiles now I want to test it and insert this into the table...
  insert into suborder values (1,1,'12-Jan-13','13-Jan-14', 'Office Depot', 1)</p>
  
  <blockquote>
    <p>Error: Error starting at line 1 in command: insert into suborder
    values (1,1,'12-Jan-13','13-Jan-14', 'Office Depot', 1) Error report:
    SQL Error: ORA-01422: exact fetch returns more than requested number
    of rows ORA-06512: at "BB.SUBORDER_DATE", line 4 ORA-04088: error
    during execution of trigger 'BB.SUBORDER_DATE'
    01422. 00000 -  "exact fetch returns more than requested number of rows"
    *Cause:    The number specified in exact fetch is less than the rows returned.
    *Action:   Rewrite the query or change number of rows requested</p>
  </blockquote>
  
  <p>With the help of others I finally got the output that I wanted when I edited the command for myself. Thank you!</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER suborder_date BEFORE
    INSERT ON suborder FOR EACH row DECLARE reqship DATE; orderdate date;
    BEGIN
      SELECT o.orddate
      INTO orderdate
      FROM orders o
      WHERE o.orderno = :new.orderno;
      IF orderdate     &gt; :new.reqshipdate THEN
        raise_application_error(-20002, ('Required Ship Date is before Order Date'));
      END IF;
    END;
  </code></pre>