如何编写为任何表设置一列的INSTEAD OF INSERT触发器?

所以看起来您使用多个模式来传递存储信息,同时保持对象名称的一致性,每个存储只有一个模式,是吗?以及某种连接/用户魔法,以便查询能够访问正确的视图。

,假定存储视图包含基表中的所有列 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

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

,假定与(1)相同,但StoreId包含在store视图中:

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 * ,如果基表发生更改,只需使用 sp_refreshview INSTEAD OF INSERT 一开始就相当昂贵的触发器。伊恩,

• 基础开销 而不是插入 触发器->填充成本 inserted x .
• #inserted 插入 十
• 更新成本 ->关于
• 骇客总开销#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(''), 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

所以, 我说得对,每个商店都有自己的ID。DB部署到每个商店,DB应该根据部署的位置记录不同的StoreId,只需最少的代码工作。这是我的建议。在数据库中创建一个表来保存StoreId。创建一个函数来检索该StoreId。然后在每个表中创建StoreId列作为使用该函数的计算列。因此,在每个部署中,唯一的更改是更新一个表中的StoreId。比如:

/* 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

StoreId 不为NULL,并给它一个默认值99?

您可以尝试将AFTER INSERT、UPDATE触发器作为INSTEAD-OF触发器的替代方法

create trigger tr_Tenant_fluff on AcmeBatWings
AFTER insert, update
as

-- You'll need to get @StoreID here somehow

update AcmeBatWings 
set StoreID = @StoreID
where [Name] IN (SELECT [Name] FROM inserted) -- update based on primary key

只是被这个老问题绊倒了: