具有專用“路由”列的分區視圖

我有一個應用程序（確切地說是 Microsoft Dynamics NAV），它將不同公司的數據拆分到具有完全相同結構的表中。

我無法改變這種行為，因此我建構了 VIEW，以便更輕鬆地處理用於報告目的的數據，如下所示：

CREATE VIEW [ALL COMPANIES$Employee]
AS
SELECT 'Company 1' as Company, * FROM [Company 1$Employee]
UNION ALL
SELECT 'Company 2' as Company, * FROM [Company 2$Employee]
UNION ALL
SELECT 'Company 3' as Company, * FROM [Company 3$Employee]
UNION ALL
SELECT 'Company 4' as Company, * FROM [Company 4$Employee]

這很適合這種read情況。

現在我還想使用這些視圖來更輕鬆地與其他應用程序互動，即inserting 和updateing 數據。

我確實知道分區視圖，但這並不完全相同，因為我沒有具有 CHECK 約束的特定列，但是必須僅使用新引入的列的資訊來“路由”到基礎表Company。

有這樣的嗎？否則，我很高興所有其他提議的解決方案使我免於使用動態 SQL。

如果您不能使用分區視圖，一種方法是INSTEAD OF觸發器，如下例所示。請注意，UPDATE觸發器假定在初始插入後永遠不會更改主鍵值。

CREATE TRIGGER [TRI ALL COMPANIES$Employee] ON
   [ALL COMPANIES$Employee]
   INSTEAD OF INSERT
AS

INSERT INTO [Company 1$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 1';

INSERT INTO [Company 2$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 2';

INSERT INTO [Company 3$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 3';

INSERT INTO [Company 4$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 4';
GO

CREATE TRIGGER [TRU ALL COMPANIES$Employee] ON
   [ALL COMPANIES$Employee]
   INSTEAD OF UPDATE
AS

UPDATE target
SET
     Col2 = inserted.Col2
   , Col3 = inserted.Col3
FROM [Company 1$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 1';

UPDATE target
SET
     Col2 = inserted.Col2
   , Col3 = inserted.Col3
FROM [Company 2$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 2';

UPDATE target
SET
     Col2 = inserted.Col2
   , Col3 = inserted.Col3
FROM [Company 3$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 3';

UPDATE target
SET
     Col2 = inserted.Col2
   , Col3 = inserted.Col3
FROM [Company 4$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 4';
GO

CREATE TRIGGER [TRD ALL COMPANIES$Employee] ON
   [ALL COMPANIES$Employee]
   INSTEAD OF DELETE
AS

DELETE target
FROM [Company 1$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 1';

DELETE target
FROM [Company 2$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 2';

DELETE target
FROM [Company 3$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 3';

DELETE target
FROM [Company 4$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 4';
GO

引用自：https://dba.stackexchange.com/questions/128756