title: Oracle trigger checks previous record before insert/update
body: <p>I would like to create a trigger that prevents a student from enrolling into a new module if he has any outstanding bills. </p>
  
  <pre><code>studID    studNRIC     paymentStatus
  -------------------------------------
  200       F7654672F      Non Payment
  </code></pre>
  
  <p>it would reject the following statement:</p>
  
  <pre><code>INSERT INTO student(studID, studNRIC, paymentStatus) 
    VALUES (201, 'F7654672F', 'Good');
  </code></pre>
  
  <p>I've came out with the following trigger but I'm still able to insert a new student. </p>
  
  <pre><code>set define off;
  CREATE OR REPLACE TRIGGER reject_new_account
  AFTER INSERT OR UPDATE ON Student
  FOR EACH ROW
  DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
      totover NUMBER(3);
  BEGIN
  SELECT COUNT (*)
  INTO   totover
  FROM   Student
  WHERE :NEW.nric = student.nric
    AND :NEW.paymentStatus = 'Non Payment';
  
  IF totover &gt; 0 THEN
     RAISE_APPLICATION_ERROR ( -20002, 
      'Student' || :NEW.nric ||
      ' has outstanding bills' );
  END IF;
  END;
  /
  </code></pre>
  
  <p>there's seems to be a problem with line 13 <code>AND :NEW.paymentStatus = 'Non Payment';</code>
  so how do I go about doing this?</p>
  
  <p>table structure</p>
  
  <pre><code>CREATE TABLE Student(
  studID              INTEGER NOT NULL,
  firstName           CHAR(25) NULL,
  lastName            CHAR(25) NULL,
  NRIC                CHAR(9) NOT NULL,
  paymentStatus       CHAR(25) Default 'Good',
  CONSTRAINT stud_Pkey PRIMARY KEY (studID),
  CONSTRAINT studPaymentStatus_type CHECK (PaymentStatus IN ('Late Payment', 'Non Payment', 'Good'))
  );
  </code></pre>