我正在开发一个遗留应用程序,该应用程序正在扩展以在多租户配置中运行.基本体系结构采用旧应用程序,并为每个表添加StoreID列.然后,每个租户通过一组过滤商店ID的视图来查看旧表,例如：
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新手！
解决方法
因此,您似乎使用多个模式来传达商店信息,同时保持对象名称一致,每个商店有一个模式,是吗？以及某种连接/用户魔术,以便查询正在查看正确的视图.
如果是这样,我提出两个令人震惊的黑客和一个推荐的解决方案(所以你知道你的选择).
EgregIoUs hack#1,假设商店视图包括除了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
如果您曾向基表添加列,则必须更新所有商店视图以包含该列,否则触发器将中断.
EgregIoUs hack#2,假定与(1)相同,只是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) SELECT * INTO #inserted FROM inserted UPDATE #inserted SET StoreId = @StoreId INSERT dbo.data SELECT * FROM #inserted END
hack#2优于hack#1的好处是您可以使用SELECT *定义商店视图,如果基表发生更改,您只需使用sp_refreshview重新编译所有商店视图.缺点是您将插入的数据从一个中间表复制到另一个中间表,并更新第二个表.这使你的INSTEAD OF INSERT触发器的开销增加了两倍,这开始时已经相当昂贵了.即
> INSTEAD OF INSERT触发器的基本开销 – >填充插入的费用 – > X.
>从插入填充#inserted的成本 – >关于x.
>更新成本#inserted – >关于x
>严重破解的总开销#2：大约3倍
否则,最好的办法是将触发器编写出来.一旦熟悉了系统表,这是一个相当简单的过程,您可以根据需要调整触发器生成.就此而言,您也应该编写商店视图的脚本.
为了帮助您入门：
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(''),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
