Can I use a trigger to add combinations of foreign keys?
<p>I'm in the situation where I want to do multiples inserts on a table with a trigger after insert.</p>

<p>Here the python code to understand the objective first:</p>

<pre><code>d = dict()
d["Table1"] = ["1", "2"]
d["Table2"] = ["A","B"]

from itertools import product
d["Table12"] = [ (t1,t2,-1) for t1, t2 in product(d["Table1"], d["Table2"])]
</code></pre>

<p>Here the result: Table12 is the product of values of d. </p>

<pre><code>{'Table1': ['1', '2'], 
 'Table2': ['A', 'B'], 
 'Table12': [('1', 'A', -1), ('1', 'B', -1), ('2', 'A', -1), ('2', 'B', -1)]}
</code></pre>

<p>Using a database I'm trying to have the same behavior with a trigger, and fully complete the association with all combinations of primary keys.</p>

<pre><code>Table1:
 pk name1 VARCHAR

Table 2:
 pk name2 VARCHAR

Table 12:
  pk (name1, name2)
  val INTEGER
  fk (t1_name) reference Table1 (name1)
  fk (t2_name) reference Table2 (name2)


CREATE TRIGGER table1_insert AFTER INSERT ON Table1
FOR EACH ROW
BEGIN
INSERT INTO Table12 VALUES(new.name1, #?, -1)
END
</code></pre>

<p>Is there a way to get the product #? with something like</p>

<blockquote>
  <p>INSERT INTO Table12 VALUES(new.name2, table2.name2, -1) FROM select *
  in table2;</p>
</blockquote>

<p>If "3" is inserted in Table1: Table12 must be completed with <strong>(3, 'A', -1), (3, 'B', -1)</strong>.</p>

<p>Use a <code>SELECT</code> query in the <code>INSERT</code> query.</p>

<pre><code>CREATE TRIGGER table1_inisert AFTER INSERT ON Table1
FOR EACH ROW
BEGIN
    INSERT INTO Table12 (name1, name2, val)
    SELECT NEW.name1, name2, -1
    FROM Table2
END
</code></pre>

python mysql database-design foreign-keys database-trigger