title: MySQL before insert trigger that drops insert if condition
body: <p>I have the following tables:</p>
  
  <p>slds:</p>
  
  <pre><code>+-----------+-----------+
  |    id     | sld_name  |  
  +-----------+-----------+
  |     1     |  google   |  
  +-----------+-----------+
  |     2     |  github   |
  +-----------+-----------+
  </code></pre>
  
  <p>paths:</p>
  
  <pre><code>+-----------+------------+----------+
  |    id     | path_name  |  sld_id  |   
  +-----------+------------+----------+
  |    101    |  /cats/    |    1     |
  +-----------+------------+----------+
  |    102    |  /dogs/    |    2     |
  +-----------+------------+----------+
  </code></pre>
  
  <p>Note that the <code>sld_name</code> is a unique index, and <code>sld_id</code> is the foreign key of <code>slds</code>.</p>
  
  <pre><code>INSERT IGNORE INTO paths (path_name, sld_id)
  VALUES ('/dogs/', 1), ('/dogs/', 2) ... this can be hundreds of rows long
  </code></pre>
  
  <p>When the example above happens, I need a way to remove/prevent the insert of the additional <code>('/dogs/', 2)</code> row, since that path already exists where <code>sld_id=2</code>, and not prevent the <code>('/dogs/', 1)</code> row, since <code>sld_id=1</code> doesn't have a <code>/dogs/</code> path yet.</p>
  
  <p>To implement this, I tried using this trigger:</p>
  
  <pre><code>delimiter $$
  create trigger after_insert_paths
  after insert on paths 
  for each row begin
  declare path_check INT;
  
  set path_check := (
      select sld_id 
      from paths 
      where path_name=new.path_name and sld_id=new.sld_id
  );
  if path_check is not null then 
      set new.path_name = null;
  end if;
  
  end $$
  delimiter ;
  </code></pre>
  
  <p>All this did was prevent inserts from happening at all.  </p>
  
  <p>Is there something specific that is wrong with the trigger? or does this strategy not work in general?</p>
  
  <p>Is there a better approach to this that I'm missing?</p>
  
  <p>Any advice would really be appreciated! :)</p>