title: Array and loop in a SQL Server trigger, how to?
body: <p>I am creating a trigger on a table in a SQL Server database.</p>
  
  <p>But what I am unable to do is this: I have variables <code>@pageid</code> and <code>@templateid</code> populated, and I need to read all template column id's from one table - basically get a list of them (they depend on the template id and can vary in number) so I need some way of making an array or something and go through it and add it to a table.</p>
  
  <p>So...</p>
  
  <ol>
  <li><p>how do I read all template id's (image below: from 1st table (available template fields) - <code>where availabletemplateid is @templateid</code>) into an array?</p></li>
  <li><p>how do I loop through this array and add them one by one it to another table (image below: 2nd table - (public page to field mapping) with <code>publicpageid = @pageid</code> and fieldid is one by one from the previous step, and field content is "-")</p></li>
  </ol>
  
  <p><img src="https://i.stack.imgur.com/KH7MM.png" alt="enter image description here"></p>
  
  <p>So far I have</p>
  
  <pre><code>ALTER TRIGGER CreatePageFields
  ON dbo.PublicPages
  FOR INSERT
  AS
  BEGIN TRANSACTION
  
  /* variables */
  DECLARE
      @pageid bigint,
      @templateid bigint
  
  /* insert template fields for this page */
  SELECT @pageid = id, @templateid = templateid FROM inserted
  
  /* TODO 1 get all template field id's */
  /* TODO 2 insert each templatefieldid / page combination */    
  
  /* execute */
  COMMIT TRANSACTION
  
  GO
  </code></pre>
  
  <p>Thanks a lot</p>