title: How can I write an INSTEAD OF INSERT trigger that sets one column for any table?
body: <p>I am working on a legacy app that is being extended to run in a multi-tenant configuration.  The basic architecture takes the old application and adds a <code>StoreID</code> column to every table.  Each tenant then sees the legacy tables through a set of views that filter on store id, something like:</p>
  
  <pre><code>create view AcmeBatWings.data as 
  select * from dbo.data d where d.StoreId = 99
  </code></pre>
  
  <p>It is a bit fancier than that, but this simplifies the question.</p>
  
  <p>Now, I can create a trigger like this</p>
  
  <pre><code>create trigger tr_Tenant_fluff on AcmeBatWings
  instead of insert
  as
  insert into AcmeBatWings (Name, StoreId)
  select i.Name, 99 from inserted i
  </code></pre>
  
  <p>Assuming a simple table with Name and StoreId columns.</p>
  
  <p>My problem is that I have 100+ tables and if I was going to follow this pattern I would have to make a specialized trigger for each table listing all the fields for each of them.  Not only is that annoying in the short term is is a maintenance nightmare since any table changes would need to include trigger modifications.</p>
  
  <p>So, how could a write a trigger that just says on every insert or update set the StoreId field to 99 for any table with a StoreId?</p>
  
  <p>Thanks for helping a SQL newbie out!</p>