title: PL/SQL Triggers Library Infotainment System
body: <p>I am trying to make a Library Infotainment System using PL/SQL. Before any of you speculate, yes it is a homework assignment but I've tried hard and asking a question here only after trying hard enough. </p>
  
  <p>Basically, I have few tables, two of which are:
  <code>Issue(Bookid, borrowerid, issuedate, returndate)</code> and <code>
  Borrower(borrowerid, name, status)</code>.</p>
  
  <p>The status in <code>Borrower</code> table can be either <code>'student' or 'faculty'</code>. I have to implement a restriction using trigger, that per <code>student</code>, I can issue only 2 books at any point of time and per <code>faculty</code>, 3 books at any point of time.</p>
  
  <p>I am totally new to PL/SQL. It might be easy, and I have an idea of how to do it. This is the best I could do. Please help me in finding design/compiler errors.</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER trg_maxbooks
  AFTER INSERT ON ISSUE
  FOR EACH ROW 
  DECLARE
      BORROWERCOUNT INTEGER;
      SORF VARCHAR2(20);
  BEGIN
      SELECT COUNT(*) INTO BORROWERCOUNT
      FROM ISSUE
      WHERE BORROWER_ID = :NEW.BORROWER_ID;
  
      SELECT STATUS INTO SORF
      FROM BORROWER
      WHERE BORROWER_ID = :NEW.BORROWER_ID;
  
      IF ((BORROWERCOUNT=2 AND SORF='STUDENT')
          OR (BORROWERCOUNT=3 AND SORF='FACULTY')) THEN
          ROLLBACK TRANSACTION;
      END IF;
  END;
  </code></pre>