title: triggers are slow
body: <p>I am working on an SQLite-powered C++ application and am using the basic C API for accessing SQLite databases. I have two tables RIV_PATHS and RIV_INTERSECTIONS, where RIV_PATHS contains a primary key and Intersection contains multiple foreign keys referring to table RIV_PATHS (a path consists of multiple intersections). Whenever a path is removed, all of the intersection rows should be removed. Whenever ALL of the intersections points are removed, the path should be removed:</p>
  
  <pre><code>CREATE TRIGGER PATHS_DELETE
  AFTER DELETE ON RIV_PATHS
  FOR EACH ROW
  BEGIN
      DELETE FROM RIV_INTERSECTIONS WHERE pid = OLD.id;
  END;
  
  CREATE TRIGGER ISECTS_DELETE 
  AFTER DELETE ON RIV_INTERSECTIONS
  FOR EACH ROW
  WHEN ((select count() from  RIV_INTERSECTIONS where pid = OLD.pid) = 0)
  BEGIN
    DELETE FROM RIV_PATHS WHERE id = OLD.pid ;
  END;
  </code></pre>
  
  <p>This is very slow, I think it is because of some circular triggering, but am unsure on how to avoid this behaviour (if at all possible). I have already tried changing one of the triggers to before but with no success.</p>
  
  <p>I also have the same issues with inserts triggers for these table (not displayed).</p>
  
  <p>The database schema is as such: The two RIV_* tables I use as a sort of view (but not views because I cannot insert/update those in SQLite) of some other tables. I create them by a <code>SELECT * FROM</code>.</p>
  
  <pre><code>CREATE TABLE RIV_INTERSECTIONS(
    PID INT,
    BOUNCE_NR INT,
    POS_X REAL,
    POS_Y REAL,
    POS_Z REAL,
    PRIMITIVE_ID INT,
    SHAPE_ID INT,
    OBJECT_ID INT,
    SPECTRUM_R REAL,
    SPECTRUM_G REAL,
    SPECTRUM_B REAL,
    INTERACTION_TYPE INT,
    LIGHT_ID INT
  );
  CREATE TABLE RIV_PATHS(
    ID INT,
    IMAGE_X INT,
    IMAGE_Y INT,
    LENS_U REAL,
    LENS_V REAL,
    TIMESTAMP REAL,
    THROUGHPUT_ONE REAL,
    THROUGHPUT_TWO REAL,
    THROUGHPUT_THREE REAL,
    RADIANCE_R REAL,
    RADIANCE_G REAL,
    RADIANCE_B REAL
  );
  </code></pre>