title: SQLite trigger insert/replace multiple rows multiple tables
body: <p>I am attempting to respond to the insertion of a row in one table (A) to create or update multiple rows in a second table (B) based on the values of a third table (C) (which can be joined to the first).</p>
  
  <p>I have the following construct,</p>
  
  <pre><code>CREATE TRIGGER MyTrigger AFTER INSERT ON A
      BEGIN
          INSERT OR REPLACE INTO B (ID, T1, T2, Role)
          VALUES
          (
              ( SELECT ID FROM C WHERE R1 = NEW.R1 ),
              NEW.T1,
              B.T2, -- The existing row's T2
              ( SELECT Role FROM C WHERE R1 = NEW.R1 ),
          )
      END;
  
  Table A has columns ID, T1, R1
  Table B has columns ID, T1, T2, Role
  Table C has columns ID, R1, R2, Role
  </code></pre>
  
  <p>I have at least two problems with my attempts at composing the trigger</p>
  
  <ol>
  <li>I don't know how to reference B's existing values in the REPLACE case, thus the "B.T2"</li>
  <li>I don't know how to reference multiple columns (R1, Role) from the same row in table C when doing my INSERT/REPLACE in table B.</li>
  </ol>
  
  <p>Thanks for any help in sorting this out.</p>