sql - 如何编写 INSTEAD OF INSERT 触发器来为任何表设置一列？

Question

我正在开发一个遗留应用程序，该应用程序正在扩展以在 Multi-Tenancy 配置中运行。基本架构采用旧应用程序，并向每个表添加一个 StoreID 列。然后，每个租户通过一组根据商店 ID 进行筛选的 View 来查看旧表，例如:

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

它比这更奇特，但这简化了问题。

现在，我可以创建这样的触发器

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

假设一个包含 Name 和 StoreId 列的简单表。

我的问题是我有 100 多个表，如果我要遵循这种模式，我必须为每个表创建一个专门的触发器，列出每个表的所有字段。这不仅在短期内令人烦恼，而且还是维护的噩梦，因为任何表更改都需要包括触发器修改。

那么，如何编写一个触发器，在每次插入或更新时将任何具有 StoreId 的表的 StoreId 字段设置为 99？

感谢您帮助 SQL 新手!

Answer 1

看来您正在使用多个模式来传达商店信息，同时保持对象名称一致，每个商店使用一个模式，是吗？以及某种连接/用户魔法，以便查询到达正确的 View 。

如果是这样，我会提出两个令人震惊的黑客攻击和一个推荐的解决方案(以便您知道自己的选择)。

Egregious hack #1，假设商店 View 包含基表中的所有列除了 StoreId，其顺序位置与基表相同，并且没有其他列:

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

如果您向基表添加一列，则必须更新所有存储 View 以包含该列，否则触发器将中断。

Egregious hack #2，假设与 (1) 相同，但 StoreId 包含在商店 View 中:

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

与 hack #1 相比，hack #2 的好处是您可以使用 SELECT * 定义您的商店 View 。 ，如果基表发生变化，您只需使用 sp_refreshview 重新编译所有商店 View 。 。缺点是您要将插入的数据从一个中间表复制到另一个中间表，并更新第二个表。这是您的 INSTEAD OF INSERT 的开销的三倍触发器，一开始就已经相当昂贵了。即，

• 基本开销为INSTEAD OF INSERT触发 -> 填充成本 inserted -> x 。
• 填充成本#inserted来自inserted -> 关于x 。
• 更新费用 #inserted -> 关于x
• 令人震惊的黑客攻击 #2 的总开销:约 3 x

因此，否则，最好的办法就是编写触发器脚本。这是一个相当简单的过程，一旦您熟悉了系统表，您就可以以您认为合适的方式调整触发器生成。就此而言，您还应该编写商店 View 的脚本。

让您开始:

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