title: IF NOT EXISTS in trigger
body: <p>I have tow tables concept_access and concept_access_log. I want to create a trigger that works every time something is deleted from concept_access, check if there is similar record in log table and if not, inserts new one before it is deleted from concept_access.</p>
  
  <p>I modified trigger and now it looks like this:</p>
  
  <pre><code>DROP TRIGGER IF EXISTS before_delete_concept_access;
  DELIMITER //
  CREATE TRIGGER before_delete_concept_access
      BEFORE DELETE ON `concept_access` FOR EACH ROW
      BEGIN
          IF (SELECT 1 FROM concept_access_log WHERE map=OLD.map 
                            AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) IS NULL THEN
              INSERT INTO concept_access_log (map, accesstype, startdate, stopdate)
              VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate);
          END IF;
      END//
  DELIMITER ;
  </code></pre>
  
  <p>Sample data in concept_access before delete:</p>
  
  <pre><code>map accesstype  startdate   stopdate
  1   public      NULL        NULL    
  1   loggedin    2011-05-11  NULL    
  1   friends     NULL        NULL    
  </code></pre>
  
  <p>Log table already has first 2 rows. And they are exactly the same as in concept_access. When I delete first row from concept_access table, I get this in log table:</p>
  
  <pre><code>map accesstype  startdate   stopdate
  1   public      NULL        NULL    
  1   loggedin    2011-05-11  NULL    
  1   friends     NULL        NULL    
  1   public      NULL        NULL    
  </code></pre>
  
  <p>While it is not supposed to insert anything because (1,public,null,null) already exists there.</p>
  
  <p>This table has no primary key. I was not creating structure, so don't ask me why. Changing it will ruin a lot of already existing functionality. I just need to keep log of what was removed from table <code>concept_access</code> and store it in log without duplicates.</p>
  
  <p>I would really appreciate, if anyone can figure out what is going wrong.</p>