Maxic_unrelax

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'