How can I write an INSTEAD OF INSERT trigger that sets one column for any table?

Question

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 StoreID column to every table. Each tenant then sees the legacy tables through a set of views that filter on store id, something like:

create view AcmeBatWings.data as 
select * from dbo.data d where d.StoreId = 99

It is a bit fancier than that, but this simplifies the question.

Now, I can create a trigger like this

create trigger tr_Tenant_fluff on AcmeBatWings
instead of insert
as
insert into AcmeBatWings (Name, StoreId)
select i.Name, 99 from inserted i

Assuming a simple table with Name and StoreId columns.

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.

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?

Thanks for helping a SQL newbie out!

Answer 1

So it appears you are using multiple schemas to convey the store information while keeping object names consistent, w/ one schema per store, yes? And some sort of connection/user magic so that queries are hitting the right views.

If so, I present two egregious hacks and one recommended solution (so you know your options).

Egregious hack #1, assumes the store views include all columns from the base table except StoreId, in the same ordinal position as the base table, and no other columns:

CREATE TRIGGER tr_Tenant_fluff ON AcmeBatWings.data
INSTEAD OF INSERT 
AS BEGIN 
  DECLARE @StoreId INT

  SELECT @StoreId = StoreId FROM dbo.StoreSchemas 
  WHERE StoreSchema = OBJECT_SCHEMA_NAME(@@PROCID)

  INSERT dbo.data SELECT *, @StoreId FROM inserted
END

If you ever add a column to the base table, you would have to update all store views to include the column, or the triggers will break.

Egregious hack #2, assumes the same as (1), except that StoreId is included in the store views:

CREATE TRIGGER tr_Tenant_fluff ON AcmeBatWings.data
INSTEAD OF INSERT 
AS BEGIN 
  DECLARE @StoreId INT

  SELECT @StoreId = StoreId FROM dbo.StoreSchemas 
  WHERE StoreSchema = OBJECT_SCHEMA_NAME(@@PROCID)

  SELECT * INTO #inserted FROM inserted
  UPDATE #inserted SET StoreId = @StoreId

  INSERT dbo.data SELECT * FROM #inserted
END

The benefits of hack #2 over hack #1 is that you can define your store views with SELECT *, and if the base tables change, you simply recompile all store views with sp_refreshview. The downside is that you are copying inserted data from one intermediary table to another, and updating the second table. This is has tripled the overhead of your INSTEAD OF INSERT trigger, which was already fairly expensive to begin with. ie,

• base overhead of INSTEAD OF INSERT trigger -> cost to populate inserted -> x.
• cost to populate #inserted from inserted -> about x.
• cost to update #inserted -> about x
• total overhead of egregious hack #2: about 3x

So otherwise, the best thing to do is script the triggers out. It's a fairly straight-forward process, once you are familiar the system tables, and you can tweak the trigger generation anyway you see fit. For that matter, you should be scripting out the store views as well.

To get you started:

CREATE TABLE dbo.data (Name VARCHAR(10), StoreId INT)
GO
CREATE SCHEMA StoreA
GO
CREATE SCHEMA StoreB
GO
CREATE SCHEMA StoreC
GO
CREATE VIEW StoreA.data AS SELECT Name FROM dbo.data WHERE StoreId = 1
GO
CREATE VIEW StoreB.data AS SELECT Name FROM dbo.data WHERE StoreId = 2
GO
CREATE VIEW StoreC.data AS SELECT Name FROM dbo.data WHERE StoreId = 3
GO
CREATE TABLE dbo.StoreSchemas (StoreSchema SYSNAME UNIQUE, StoreId INT PRIMARY KEY)
GO
INSERT dbo.StoreSchemas VALUES ('StoreA', 1), ('StoreB', 2), ('StoreC', 3)
GO

DECLARE @crlf NCHAR(2) = NCHAR(13)+NCHAR(10)
SELECT
  N'CREATE TRIGGER tr_Tenent_fluff ON '+schema_name(v.schema_id)+N'.data'+@crlf
+ N'INSTEAD OF INSERT'+@crlf
+ N'AS BEGIN'+@crlf
+ N'  INSERT dbo.data ('
+ STUFF((
    SELECT @crlf+N'  , '+name FROM sys.columns tc 
    WHERE tc.object_id = t.object_id
      AND (tc.name IN (SELECT name FROM sys.columns vc WHERE vc.object_id = v.object_id)
        OR tc.name = N'StoreId')
    ORDER BY tc.column_id
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
    ,5,1,N' ')+@crlf
+ N'  )'+@crlf
+ N'  SELECT'
+ STUFF((
    SELECT @crlf+N'  , '+name
      + CASE WHEN name = N'StoreId' THEN ' = '+(
          SELECT CONVERT(NVARCHAR,StoreId) FROM dbo.StoreSchemas s 
          WHERE s.StoreSchema = SCHEMA_NAME(v.schema_id)
          )
        ELSE '' END
    FROM sys.columns tc 
    WHERE tc.object_id = t.object_id
      AND (tc.name IN (SELECT name FROM sys.columns vc WHERE vc.object_id = v.object_id)
        OR tc.name = N'StoreId')
    ORDER BY tc.column_id
    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)')
    ,5,1,N' ')+@crlf
+ N'  FROM inserted'+@crlf
+ N'END'+@crlf
+ N'GO'+@crlf
FROM sys.tables t 
JOIN sys.views v 
  ON t.name = v.name 
 AND t.schema_id = SCHEMA_ID('dbo') 
 AND v.schema_id <> t.schema_id
WHERE t.name = 'data'
GO

Answer 2

So, if I've got this right, each store has its own ID. The DB is deployed to each store and the DB should record a different StoreId based on where it has been deployed with minimal code effort. Here's what I propose. Create a table in the database to hold the StoreId. Create a function to retrieve that StoreId. Then create the StoreId column in each table as a computed column that uses the function. So, on each deployment, the only change is to update the StoreId in one table. Something like:

/* This table is updated with the unique value for each individual store */
create table MyStore (
    StoreId int
)

insert into MyStore
    (StoreId)
    values
    (99)        
go

/* This function will be used in the computed column of each table */
create function dbo.LookupStoreId()
returns int
as
begin
    return (select StoreId from MyStore)
end
go

create table AcmeBatWings (
    Name char(10),
    StoreId as dbo.LookupStoreId()
) 

insert into AcmeBatWings
    (Name)
    values
    ('abcde')

select Name, StoreId from AcmeBatWings
go

/* Clean up after demo */
drop table AcmeBatWings
drop table MyStore
drop function dbo.LookupStoreId
go