Как сохранить идентификаторы для 2 таблиц, Instead of insert trigger?

Question

[Maxic_unrelax]

Есть 3 таблицы.
1) Production.ProductModel (ProductModelID int, Name nvarchar(50) unique,...)
2) Production.ProductDescription (ProductDescriptionID int, Description nvarchar(400), ...)
3) Production.ProductModelProductDescriptionCulture (ProductModelID int, ProductDescriptionID, ...)

Третья таблица служит для связи идентификаторов (ProductModelID и ProductDescriptionID).

Задача:
Написать Instead of insert триггер, который через VIEW будет добавлять данные в нужные таблицы.

INSERT INTO productModel (Name, CatalogDescription, Instructions, [Description], CultureID)  
VALUES ('Test1' , NULL, NULL, 'Product Description', 'en'),
('Test2' , NULL, NULL, 'Product Description2', 'en')

Суть проблемы:
При добавлении одновременно нескольких записей, как получить ProductModelID и ProductDescriptionID в одной временной таблице ( либо другой вариант ), чтобы позже сохранить их связь в связывающей таблице (ProductModelProductDescriptionCulture) ?

Answer 1

[Артур Полозов]

Можно попробовать использовать таблицу синхронизации и через OUTPUT в инсертах сохранять получаемый Id и его ИД в таблице связи.

например через табл. переменные и мерж:

--DROP TABLE #ProductModel
CREATE TABLE #ProductModel (ProductModelID INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(50))

--DROP TABLE #SyncTable
CREATE TABLE #SyncTable (SyncId INT IDENTITY(1,1) NOT NULL, ProductName NVARCHAR(50), ProductDescription NVARCHAR(50) ) /*, тут другие поля  ); */

INSERT INTO #SyncTable (ProductName, ProductDescription)   -- таблица связей ProductName vs ProductDescription; общее SyncID
VALUES ('test2','Description2')
        ,('test5','Description5')
        ,('test3','Description3')


-- для первой таблицы:
DECLARE @tProductModel TABLE (ProductModelID int,  SyncID int);  -- табл. переменная

MERGE #ProductModel P
            USING #SyncTable S ON (S.ProductName = P.ProductName)
            WHEN NOT MATCHED BY TARGET  -- не совпало по имени, вставляем:
                THEN INSERT (ProductName) 
                     VALUES (S.ProductName)
            OUTPUT   INSERTED.ProductModelID, S.SyncId INTO  @tProductModel (ProductModelID, SyncID);  

SELECT * FROM @tProductModel AS tpm  -- тут связь полученного ProductModelID с известным SyncId

-- аналогично для второй таблы

Answer 2

[Maxic_unrelax]

Артур Полозов, спасибо! Получилось сделать с помощью Merge и OUTPUT. Вот результат:

USE AdventureWorks2012
GO
-- a)
CREATE VIEW productModel
WITH ENCRYPTION, SCHEMABINDING AS
	SELECT 
		pm.ProductModelID, pm.Name, pm.CatalogDescription, pm.Instructions,
		pd.[Description],
		ppdc.CultureID,
		c.Name as CultureName
	FROM Production.ProductModel AS pm
	JOIN Production.ProductModelProductDescriptionCulture AS ppdc
		ON pm.ProductModelID = ppdc.ProductModelID
	JOIN Production.ProductDescription AS pd
		ON ppdc.ProductDescriptionID = pd.ProductDescriptionID
	JOIN Production.Culture AS c
		ON ppdc.CultureID = c.CultureID
GO

CREATE UNIQUE CLUSTERED INDEX productModelIndex
    ON productModel (ProductModelID,CultureID);
GO
-- b)
---- INSERT
CREATE TRIGGER trigger_productModel_INSERT
ON productModel
INSTEAD OF INSERT AS
BEGIN
	DECLARE @ProductModel AS TABLE (
		ProductModelID INT,
		Name NVARCHAR(50)
	);
	DECLARE @ProductDescription AS TABLE (
		ProductDescriptionID INT,
		Name NVARCHAR(50)
	);
	-- ProductModel
	MERGE Production.ProductModel AS pm
	USING INSERTED AS ins
	ON 1 = 0
	WHEN NOT MATCHED
		THEN INSERT (Name, CatalogDescription, Instructions)
				VALUES (ins.Name, ins.CatalogDescription, ins.Instructions)
	OUTPUT	INSERTED.ProductModelID,
			ins.Name
			INTO @ProductModel
			(
			ProductModelID,
			Name
			);
	-- Description
	MERGE Production.ProductDescription AS pd
	USING INSERTED AS ins
	ON 1 = 0
	WHEN NOT MATCHED
		THEN INSERT ([Description])
				VALUES (ins.[Description])
	OUTPUT	INSERTED.ProductDescriptionID,
			ins.Name
			INTO @ProductDescription
			(
			ProductDescriptionID,
			Name
			);
	-- ProductModelProductDescriptionCulture
	INSERT INTO Production.ProductModelProductDescriptionCulture (ProductModelID, ProductDescriptionID, CultureID)
		SELECT
			pm.ProductModelID,
			pd.ProductDescriptionID,
			ins.CultureID
		FROM @ProductModel AS pm
		JOIN @ProductDescription AS pd
			ON pm.Name = pd.Name
		JOIN INSERTED AS ins
			ON pm.Name = ins.Name
END
GO
---- UPDATE
CREATE TRIGGER trigger_productModel_UPDATE
ON productModel
INSTEAD OF UPDATE AS
BEGIN
	-- Production.ProductModel
	UPDATE PM
		SET Name = ins.Name, CatalogDescription = ins.CatalogDescription, Instructions = ins.Instructions
	FROM Production.ProductModel PM
	JOIN INSERTED ins
		ON ins.ProductModelID = PM.ProductModelID

	-- Production.ProductDescription
	UPDATE PD
	SET [Description] = ins.[Description]
	FROM Production.ProductDescription PD
	JOIN Production.ProductModelProductDescriptionCulture pmpdc
		ON PD.ProductDescriptionID = pmpdc.ProductDescriptionID
	JOIN INSERTED ins
		ON ins.ProductModelID = pmpdc.ProductModelID
END
GO
---- DELETE
CREATE TRIGGER trigger_productModel_DELETE
ON productModel
INSTEAD OF DELETE AS
BEGIN
	DECLARE @ProductModelProductDescriptionCulture AS TABLE (
		ProductModelID INT,
		ProductDescriptionID INT
	);

	INSERT INTO @ProductModelProductDescriptionCulture
	SELECT pmpdc.ProductModelID, pmpdc.ProductDescriptionID FROM DELETED del
	JOIN Production.ProductModelProductDescriptionCulture pmpdc
		ON del.ProductModelID = pmpdc.ProductModelID

	MERGE Production.ProductModelProductDescriptionCulture AS pmpdc
	USING @ProductModelProductDescriptionCulture AS t_pmpdc
	ON pmpdc.ProductModelID = t_pmpdc.ProductModelID AND pmpdc.ProductDescriptionID = t_pmpdc.ProductDescriptionID
	WHEN MATCHED
		THEN
			DELETE;

	DELETE FROM Production.ProductModel
	WHERE ProductModelID IN (SELECT
				ProductModelID
			FROM @ProductModelProductDescriptionCulture)

	DELETE FROM Production.ProductDescription
	WHERE ProductDescriptionID IN (SELECT
				ProductDescriptionID
			FROM @ProductModelProductDescriptionCulture)
END
GO
-- c)
INSERT INTO productModel (Name, CatalogDescription, Instructions, [Description], CultureID)
	VALUES ('Test1', NULL, NULL, 'Product Description 1', 'en'),
	('Test2', NULL, NULL, 'Product Description 2', 'ar')

UPDATE productModel
SET [Description] = 'UP'
WHERE Name = 'Test1'

DELETE FROM productModel
WHERE Name = 'Test2'