title: Trigger on Oracle Sql database
body: <p>I have some issues to resolve on my sql database:</p>
  
  <ul>
  <li>ensure that a player can only have 2 yellow cards or one red</li>
  <li>a team can not have more than 5 foreign players (player who nationality is differet from French)</li>
  </ul>
  
  <p>My model is something like that:</p>
  
  <pre><code>Team
  -------
  team_id (PK)
  name
  
  Player
  -------
  player_id (PK)
  team_id (FK)
  name
  number_yellow_cards
  number_red_cards
  nationality
  </code></pre>
  
  <p>I think that trigger can resolve my problems. Here is my trigger, but doesn't work in any situation (insert/update)</p>
  
  <pre><code>create or replace TRIGGER foreign_players
  Before Insert OR UPDATE on Player
  FOR EACH ROW 
  DECLARE nr_foreign_players INTEGER;
  Begin
  
    IF INSERTING THEN
      SELECT Count(*) INTO nr_foreign_players
      FROM
          Team T
          INNER JOIN Player P ON P.team_id = T.team_id
      WHERE 
          P.team_id =  :new.team_id 
          AND P.nationality &lt;&gt; 'French';
  
      if(nr_foreign_players &gt; 5) then
          Raise_Application_Error(-20000,'Error insert' ); 
      End If;
    END IF; --INSERTING
  
    IF UPDATING THEN
      SELECT Count(*) INTO nr_foreign_players
      FROM
          Team T
          INNER JOIN Player P ON P.team_id = T.team_id
      WHERE 
          P.team_id =  :new.team_id 
          AND P.nationality &lt;&gt; 'French';
  
      if(nr_foreign_players &gt; 5) then
      Raise_Application_Error(-20000,'Error update' ); 
      End If;
    END IF; --UPDATING
  
  
  End;
  </code></pre>
  
  <p>Anyone can help me?</p>