title: How to compare two attributes from two different tables in a trigger?
body: <p>So I have two tables:</p>
  
  <pre><code>CREATE TABLE SALE(  
  OID_PA NUMBER(*,0) PRIMARY KEY,
  Amount INTEGER NOT NULL,
  Delivery_DATE DATE NOT NULL,
  OID_V NUMBER(*,0) NOT NULL,
  CONSTRAINT "AMOUNTCHECK" CHECK(Amount &gt;=0),
  FOREIGN KEY (OID_V) REFERENCES TICKET(OID_V));
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CREATE TABLE PRODUCT
  (   Code NUMBER(*,0) PRIMARY KEY,
      Stock INTEGER NOT NULL,
      Price NUMBER(4,2) NOT NULL,
      Production_Cost NUMBER NOT NULL,
      Model VARCHAR2(50) NOT NULL,
      TipoMueble VARCHAR2(50) NOT NULL,
      TipoMaterial VARCHAR2(50) NOT NULL,
  
      OID_PA NUMBER(*,0) NOT NULL,
  
      CONSTRAINT "TipoMueble" CHECK(TipoMueble IN 
      ('Canteado','Complementos','Glaciar',
      'GrupoDiseño','Tiradores','Vitrinas')),
      CONSTRAINT "TipoMaterial" CHECK(TipoMaterial IN ('Aluminio','Lacados',
      'Polilaminado','Madera','Cristal','Chapa','Granito','Formica',
      'Aglomerado','Marmol','Elementos de ferreteria')),
      CONSTRAINT "StockNegativo" CHECK(Stock &gt;=0),
      CONSTRAINT "Precio" CHECK(Price&gt;=0),
      CONSTRAINT "CosteProduccion" CHECK (Production_cost&gt;=0),
  
      FOREIGN KEY (OID_PA) REFERENCES PAQUETE(OID_PA));
  </code></pre>
  
  <p>And now I want to create a trigger, the moment I modify the attribute "Amount" in the table "SALE" then it should check the table "PRODUCT". 
  if "stock" (in PRODUCT) is less than Amount (in SALE) then </p>
  
  <pre><code>RAISE_APPLICATION_ERROR(-20003, 'Not enough stock');
  </code></pre>
  
  <p>But I don't know how to make the trigger check in both tables at the same time.
  right now my code looks likethis right now, it also has some errors that I'm not sure how to fix.</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TR_ENOUGH_STOCK
  AFTER INSERT OR UPDATE OF Amount ON SALE
   BEGIN
  
      SELECT a.Amount, b.Stock 
      FROM ( SALE a inner join PRODUCT b on (a.Code = b.Code))
  
      IF b.Stock &lt; a.Amount THEN 
          RAISE APPLICATION ERROR(-20003, 'Not enough stock');
      END IF;
  END;
  /
  </code></pre>
  
  <p>And I'm getting the following errors:</p>
  
  <pre><code>Error(209,5): PL/SQL: SQL Statement ignored
  Error(212,5): PL/SQL: ORA-00933: SQL command not properly ended
  Error(214,9): PLS-00103: Encountered the symbol "IF" when expecting one of 
  the following:     ; &lt;an identifier&gt; &lt;a double-quoted delimited-identifier&gt; 
  </code></pre>