title: Create trigger on dummy view
body: <p>I'm having problem creating a trigger. The database model look something like this:</p>
  
  <pre><code>CREATE TABLE Unit (
  SerialNO    VARCHAR(3)
  PRIMARY KEY (serialNO)
  );
  
  CREATE TABLE PackageConfig (
  PartNO      VARCHAR(15),
  SomeValue   VARCHAR(20),
  PRIMARY KEY (PartNO)
  );
  
  CREATE TABLE Package (
  ID          INT IDENTITY(1,1),
  Config      VARCHAR(15),
  PRIMARY KEY (ID),
  FOREIGN KEY (Config) REFERENCES PackageConfig(PartNO)
  );
  
  CREATE TABLE UnitInPackage (
  Package     INT,
  Unit        VARCHAR(3),
  PRIMARY KEY (Package,Unit),
  FOREIGN KEY (Package) REFERENCES Package(ID),
  FOREIGN KEY (Unit) REFERENCES Unit(SerialNO)
  );
  </code></pre>
  
  <p>There are <code>Units</code>, <code>PackageConfigurations</code> and <code>Packages</code>. A package has exactly one <code>PackageConfiguration</code> and the relation between <code>Package</code> and <code>Unit</code> is a many (units) to at most one (package).  </p>
  
  <p>What I'm trying to accomplish is a trigger on a view that would let me do the following (assuming that the <code>PackageConfig</code> and <code>Units</code> exist):</p>
  
  <pre><code>INSERT INTO v_MultiUnitPackage (PackageConfig, Unit1, Unit2, Unit3) 
  VALUES ('SomeConfig','abc','bcd','cde');
  </code></pre>
  
  <p>Which inside the trigger would do something like:</p>
  
  <pre><code>DECLARE @last INT;
  
  INSERT INTO Package (Config) VALUES ('SomeConfig');
  SET @last = SCOPE_IDENTITY();
  INSERT INTO UnitInPackage (Package,Unit)
  VALUES (@last,'abc'),(@last,'bcd'),(@last,'cde');
  </code></pre>
  
  <p>Or for more columns:</p>
  
  <pre><code>INSERT INTO Package (Config) VALUES ('SomeConfig');
  SET @last = SCOPE_IDENTITY();
  INSERT INTO UnitInPackage (Package,Unit)
  VALUES (@last,'hgf'),(@last,'gfe'),(@last,'fed'),(@last,'edc'),(@last,'dcb'),(@last,'cba');
  </code></pre>
  
  <p>To summarize, I'd like to create a trigger that takes a <code>PackageConfig</code> and N-number of <code>Units</code> and insert them in the corresponding tables.</p>
  
  <p>I've been looking into creating a dummy view that simply has the correct data types and a great enough number of columns to allow for the number of Units I want but didn't find a solution.</p>
  
  <p>I also looked into something like grouping by <code>Package.ID</code> and for each group selecting the first unit that hasn't already been selected to a previous column. Since <code>GROUP BY</code> is only usable with aggregate functions I'm unsure how to realize this idea.</p>
  
  <p>Realistically I don't see ever needing more than 5 or so Units but would prefer a generic solution to my problem.</p>
  
  <p>Perhaps there is a really simple solution that I'm just not seeing. Any help is greatly appreciated.</p>