title: Trigger at least one of each oracle
body: <p>I am creating a trigger that guarantees me that there is at least one dancer of each sex. For example, if I have only 2 dancers on that table dancers, they will have one male and one female</p>
  
  <p>I have these tables</p>
  
  <pre><code>dancers (artistic_name, sex, fnac, real_name)
  couples (name1, name2, fnac, times_together)
  </code></pre>
  
  <p>The solution principle that I had was this trigger</p>
  
  <pre><code>create or replace trigger t_control
  before insert or update on dancers
  for each row
  declare
      cnt number;
  begin
      select count(*) into cnt
      from   dancers d
      where  :new.artistic_name != d.artistic_name
      and    :new.sex = d.sex;
  
      if cnt !=0 then
          raise_application_error('-20001', 'Not possible');
      end if;
  end t_control;
  /
  </code></pre>
  
  <p>But I have the problem that if I enter first feminine, then masculine, the rest of the inserts skips the trigger</p>
  
  <p>How would you do for the expression "at least one of each"?</p>
  
  <pre><code>INSERT INTO dancers VALUES('artistic1', 'm', 'today', 'anna')    
  INSERT INTO dancers VALUES('artistic2', 'f', 'today', 'paul') 
  </code></pre>
  
  <p>From here, for each insertion the trigger jumps</p>
  
  <pre><code>ORA-20001: Not possible
  ORA-06512: at "FIDDLE_TQJRRVAQQOICTXMWSHDG.T_CONTROL", line 12
  ORA-04088: error during execution of trigger 'FIDDLE_TQJRRVAQQOICTXMWSHDG.T_CONTROL'
  
  INSERT INTO dancers VALUES('artistic3', 'f', 'today', 'mery') 
  INSERT INTO dancers VALUES('artistic4', 'm', 'today', 'joan')
  </code></pre>