title: Trigger vs constraint
body: <p>Should I use a trigger or a check constraint and can I even create a check constraint that does the following job:</p>
  
  <p>If an article is of type 'Bert', it can have only one row in mytable and only 1 in the stock.
  If it is not of type 'Bert', neither of the conditions apply.(can have more rows and more than 1 in the stock)</p>
  
  <pre><code>INSERT INTO mytable VALUES('Chip',1,'Bert'); -- Yes
  INSERT INTO mytable VALUES('Screen',1,'Bert'); -- Yes
  INSERT INTO mytable VALUES('Chip',1,'Bert'); -- No
  INSERT INTO mytable VALUES('Cable',2,'Bert'); -- No
  INSERT INTO mytable VALUES('Keyboard',2,'Non-bert'); -- Yes
  INSERT INTO mytable VALUES('Keyboard',2,'Non-bert'); -- Yes
  INSERT INTO mytable VALUES('Keyboard',3,'Non-bert'); -- Yes
  </code></pre>
  
  <p>A trigger like this is causing problems because I am trying to access the base table inside the trigger:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER trg_mytable
  BEFORE INSERT OR UPDATE ON mytable 
  FOR EACH ROW 
  WHEN (NEW.TYPEB = 'Bert')
  DECLARE
      L_COUNT NUMBER;
  BEGIN
      SELECT  COUNT(*) INTO L_COUNT
      FROM    MYTABLE 
      WHERE   ARTICLE = :NEW.ARTICLE
          AND TYPEB = :NEW.TYPEB;
  
      IF L_COUNT &gt; 0  THEN
          RAISE_APPLICATION_ERROR( -20001, 'Bert already exists!' );
      ELSIF :NEW.STOCK_COUNT &gt; 1 THEN
          RAISE_APPLICATION_ERROR( -20001, 'Can''t insert more than one with type Bert!' );
      END IF;
  END;
  </code></pre>