DROP TABLE Product
GO

CREATE TABLE Product
(
	[ProductID]     int,
	[CategoryID]     int,
	[Name]          varchar(MAX) NOT NULL,
	CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
	(
		[CategoryID] ASC,
		[ProductID] ASC
	)
)
GO

CREATE TRIGGER InsteadTrigger on Product
INSTEAD OF INSERT
AS
BEGIN

	BEGIN TRANSACTION

		DECLARE @PK INT

		SELECT @PK = ISNULL(MAX([ProductID]),0) + 1
			FROM Product
			WHERE [CategoryID] = (SELECT [CategoryID] FROM inserted)

		INSERT INTO Product ([ProductID],[CategoryID],[Name])
			SELECT @PK,[CategoryID],[Name]
			FROM inserted

	COMMIT TRANSACTION

END
GO

INSERT INTO Product([CategoryID],[Name])
	VALUES(1,'Prod 1 for Cat 1')
INSERT INTO Product([CategoryID],[Name])
	VALUES(1,'Prod 2 for Cat 1')

INSERT INTO Product([CategoryID],[Name])
	VALUES(2,'Prod 1 for Cat 2')
INSERT INTO Product([CategoryID],[Name])
	VALUES(2,'Prod 2 for Cat 2')

SELECT * FROM Product

We walk in the dark places no others will enter
We stand on the bridge and none may pass

We walk in the dark places no others will enter
We stand on the bridge and none may pass

We walk in the dark places no others will enter
We stand on the bridge and none may pass

We walk in the dark places no others will enter
We stand on the bridge and none may pass