How to store ids for 2 tables, Instead of insert trigger?

Question

Transact SQL

There are 3 tables.
1) Production.ProductModel (ProductModelID int, Name nvarchar(50) unique,...)
2) Production.ProductDescription (ProductDescriptionID int, Description nvarchar(400), ...)
3) Production.ProductModelProductDescriptionCulture (ProductModelID int, ProductDescriptionID, ...)
The third table is used to link identifiers (ProductModelID and ProductDescriptionID).
Task:
Write Instead of insert a trigger that will add data to the required tables through VIEW.

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

The essence of the problem:
When adding several records at the same time, how to get the ProductModelID and ProductDescriptionID in the same temporary table (or another option) in order to later save their relationship in the linking table (ProductModelProductDescriptionCulture) ?

Answer 1

You can try to use the synchronization table and save the received Id and its ID in the link table through OUTPUT in inserts.
for example, through the table. variables and merge:

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

Artur Polozov , thank you! It turned out to be done using Merge and OUTPUT. Here is the result:

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'