After Insert and After Update Trigger problem

Question

I am having a problem with my my After Insert and After Update triggers. When I insert a new record into the Recipe_rawMaterial table I want to check If that RecipeID and RawMaterialID exists in the RecipeRevisionNumner Table and get the RevisionNumber from that table and add it to the Recipe_RawMaterial table. If it does not exist in the RecipeRevisionNumber table create e new record With 100 as the RevisionNumber

In the After Update Trigger - If the data in Recipe_RawMaterial is updated it increaments the RevisionNumber in the RecipeRevisionNumber by 1 for those records with the same RecipeID. It gets the RevisionNumber from the RecipeRevisionNumber table where the RecipeID and the RawMaterialID is the same and Updates the Recipe_RawMaterial with that RevisionNumber

Here is the code for my triggers

USE [AdventureWorks2014]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[UpdatedRecipeRawMaterial]
ON [dbo].[Recipe_RawMaterial] 
AFTER UPDATE
AS 
IF EXISTS (
    SELECT * FROM INSERTED I 
	JOIN DELETED D ON D.RecipeID = I.RecipeID
             
    )

BEGIN 

UPDATE RecipeRevisionNumber SET RevisionNumber= RecipeRevisionNumber.RevisionNumber + 1 FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID and MyRecipeRawMaterial.RawMaterialID=I.RawMaterialID 
INNER JOIN Deleted D ON MyRecipeRawMaterial.RecipeID = D.RecipeID and MyRecipeRawMaterial.RawMaterialID=D.RawMaterialID 


DECLARE @MyRevisionNumber int=0
SELECT @MyRevisionNumber= (SELECT RecipeRevisionNumber.RevisionNumber 
							FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID  
							)


UPDATE Recipe_RawMaterial SET Recipe_RawMaterial.RevisionNumber = @MyRevisionNumber 
FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID WHERE MyRecipeRawMaterial.RecipeID=I.RecipeID

END

else

IF NOT EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D
            ON D.RecipeID = I.RecipeID  
    )
BEGIN
INSERT INTO RecipeRevisionNumber (RecipeID,RawMaterialID,RevisionNumber) SELECT MyRecipeRawMaterial.RecipeID,MyRecipeRawMaterial.RawMaterialID,100 FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial

END

GO

USE [AdventureWorks2014]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[InsertedRecipeRawMaterial]
ON [dbo].[Recipe_RawMaterial] 
AFTER INSERT
AS 
IF EXISTS (
    SELECT * 
							FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID
    )


BEGIN 

DECLARE @MyRecipeID int=0
DECLARE @MyRevisionNumber int=0
SELECT @MyRevisionNumber= (SELECT RecipeRevisionNumber.RevisionNumber 
							FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID  
							)



UPDATE Recipe_RawMaterial SET Recipe_RawMaterial.RevisionNumber = @MyRevisionNumber  
FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID WHERE MyRecipeRawMaterial.RecipeID=I.RecipeID

END

IF NOT EXISTS (
    SELECT * FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID    )

BEGIN

INSERT INTO RecipeRevisionNumber (RecipeID,RawMaterialID,RevisionNumber)
 SELECT I.RecipeID,I.RawMaterialID,100 FROM Inserted I 

 
  SELECT @MyRevisionNumber =0
  
SELECT @MyRevisionNumber= (SELECT RecipeRevisionNumber.RevisionNumber 
							FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID  
							)
							

UPDATE Recipe_RawMaterial SET Recipe_RawMaterial.RevisionNumber = @MyRevisionNumber  
FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID  and MyRecipeRawMaterial.RawMaterialID=I.RawMaterialID  WHERE MyRecipeRawMaterial.RecipeID=I.RecipeID

END


GO

But now what is happenning is that I am getting different RevisionNumbers for the same Recipe in the RecipeRevisionNumber table. When I start creating New records I start with 101 in the Recipe_RawMaterial Table instead of 100 and the RevisionNumbers in the Recipe_RawMaterial are not increamenting by 1. I am suspecting that my After Insert Trigger is calling my After Update Trigger

Here is the table structure

USE [AdventureWorks2014]
GO
/****** Object:  Table [dbo].[Recipe]    Script Date: 2019/01/16 7:53:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipe](
	[RecipeID] [int] IDENTITY(1,1) NOT NULL,
	[BusinessEntityID] [int] NOT NULL,
	[ProductID] [int] NOT NULL,
	[RevisionNumber] [nvarchar](3) NULL,
 CONSTRAINT [PK_Recipe] PRIMARY KEY CLUSTERED 
(
	[RecipeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Recipe_RawMaterial]    Script Date: 2019/01/16 7:53:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Recipe_RawMaterial](
	[RecipeID] [int] NOT NULL,
	[RawMaterialID] [int] NOT NULL,
	[RevisionNumber] [nvarchar](3) NULL CONSTRAINT [FK_Recipe_RawMaterial_RevisionNumber]  DEFAULT ((0)),
	[PercentageInMix] [decimal](18, 2) NULL CONSTRAINT [FK_Recipe_RawMaterial_PercentageInMix]  DEFAULT ((0)),
	[isIncluded] [bit] NOT NULL CONSTRAINT [DF_Recipe_RawMaterial_isIncluded]  DEFAULT ((0)),
 CONSTRAINT [PK_Recipe_RawMaterial] PRIMARY KEY CLUSTERED 
(
	[RecipeID] ASC,
	[RawMaterialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[RecipeRevisionNumber]    Script Date: 2019/01/16 7:53:42 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RecipeRevisionNumber](
	[RecipeID] [int] NOT NULL,
	[RawMaterialID] [int] NOT NULL,
	[RevisionNumber] [int] NOT NULL,
 CONSTRAINT [PK_RecipeRevisionNumber] PRIMARY KEY CLUSTERED 
(
	[RecipeID] ASC,
	[RawMaterialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Recipe] ON 

GO
INSERT [dbo].[Recipe] ([RecipeID], [BusinessEntityID], [ProductID], [RevisionNumber]) VALUES (2, 20795, 3, N'101')
GO
SET IDENTITY_INSERT [dbo].[Recipe] OFF
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 1, N'104', CAST(8.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 4, N'104', CAST(14.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 6, N'104', CAST(17.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 1, 103)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 4, 105)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 6, 104)
GO
/****** Object:  Index [UC_Recipe_Unique_Site_and_Product]    Script Date: 2019/01/16 7:53:42 AM ******/
ALTER TABLE [dbo].[Recipe] ADD  CONSTRAINT [UC_Recipe_Unique_Site_and_Product] UNIQUE NONCLUSTERED 
(
	[BusinessEntityID] ASC,
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Recipe]  WITH CHECK ADD  CONSTRAINT [FK_Recipe_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[Recipe] CHECK CONSTRAINT [FK_Recipe_Products]
GO
ALTER TABLE [dbo].[Recipe]  WITH CHECK ADD  CONSTRAINT [FK_Recipe_Site] FOREIGN KEY([BusinessEntityID])
REFERENCES [dbo].[Site] ([BusinessEntityID])
GO
ALTER TABLE [dbo].[Recipe] CHECK CONSTRAINT [FK_Recipe_Site]
GO
ALTER TABLE [dbo].[Recipe_RawMaterial]  WITH CHECK ADD  CONSTRAINT [FK_Recipe_RawMaterial_RawMaterial] FOREIGN KEY([RawMaterialID])
REFERENCES [dbo].[RawMaterial] ([RawMaterialID])
GO
ALTER TABLE [dbo].[Recipe_RawMaterial] CHECK CONSTRAINT [FK_Recipe_RawMaterial_RawMaterial]
GO
ALTER TABLE [dbo].[Recipe_RawMaterial]  WITH CHECK ADD  CONSTRAINT [FK_Recipe_RawMaterial_Recipe] FOREIGN KEY([RecipeID])
REFERENCES [dbo].[Recipe] ([RecipeID])
GO
ALTER TABLE [dbo].[Recipe_RawMaterial] CHECK CONSTRAINT [FK_Recipe_RawMaterial_Recipe]
GO
ALTER TABLE [dbo].[RecipeRevisionNumber]  WITH CHECK ADD  CONSTRAINT [FK_RecipeRevisionNumber_Recipe_RawMaterial] FOREIGN KEY([RecipeID], [RawMaterialID])
REFERENCES [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID])
GO
ALTER TABLE [dbo].[RecipeRevisionNumber] CHECK CONSTRAINT [FK_RecipeRevisionNumber_Recipe_RawMaterial]
GO
ALTER TABLE [dbo].[Recipe_RawMaterial]  WITH CHECK ADD  CONSTRAINT [CheckPercentageInMix] CHECK  (([dbo].[CheckRecipePercentageInMix]([RecipeID],[PercentageInMix])=(1)))
GO
ALTER TABLE [dbo].[Recipe_RawMaterial] CHECK CONSTRAINT [CheckPercentageInMix]
GO

Answer 1

Hi tendaimare,

When I read your description about trigger logic for the achievement, it gradually makes me confused now. So I have to read your sentences repeatedly, but I have some doubts that you need to clarify.

>>

But now what is happenning is that I am getting different RevisionNumbers for the same Recipe in the RecipeRevisionNumber table.

<<

You said that you were getting different RevisionNumbers for the same Recipe in the table "RecipeRevisionNumber ", but I haven't seen that you mentioned the RevisionNumbers for the same Recipe in the RecipeRevisionNumber table must be the same on which operation, especially at the beginning of the post. Also, for which operation, I'm not sure. INSERT OR UPDATE?

>>

When I start creating New records I start with 101 in the Recipe_RawMaterial Table instead of 100 and the RevisionNumbers in the Recipe_RawMaterial are not increamenting by 1. I am suspecting that my After Insert Trigger is calling my After Update Trigger

<<

I know that when it does update operation to the table "Recipe_RawMaterial ", the RevisionNumbers in the Recipe_RawMaterial need to be increased by 1 at the beginning of the post, why it is changed to the insert operation?

Therefore, the description is opposite to the beginning description. Could you please clarify the logic of INSERT TRIGGER, UPDATE TRIGGER? You need to use some sample data to support your description, otherwise, it is hard to understand your detailed thought.

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

After the first insert this is the result I am getting in the recipe_RawMaterial

And then in the RecipeRevisionNumbers table

The second record in the Recipe_rawMaterial table - Here the Revision Number is correct

Second record in the RecipeRevisionNumbers table

there is my first problem I wanted my revision number to be 101 for that RecipeID since I have not updated any record though my Windows app

I insert Third Record in the Recipe_rawMaterial table - here its right - The RevisionNumber should bve 101 since I have not update any record through my Winforms app

In the RecipeRevisionNumbers table

The same problem as when i inserted the second record in the Recipe_RawMaterials table all the records belonging to that particular RecipeID should be 101 since I am inserting records into database and have not updated through the Winforms App

This is the Recipe_RawMaterials table after I do the first update. This is not I expected it should be 102 since this is the first update through the Winforms App

In the RecipeRevisionNumber table - Which is wrong it should be now 102 - since its our first update through the winforms app

This is the script of the data from the server which is not what is expected

USE [AdventureWorks2014]
GO
SET IDENTITY_INSERT [dbo].[Recipe] ON 

GO
INSERT [dbo].[Recipe] ([RecipeID], [BusinessEntityID], [ProductID], [RevisionNumber]) VALUES (2, 20795, 3, N'101')
GO
SET IDENTITY_INSERT [dbo].[Recipe] OFF
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 1, N'104', CAST(18.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 4, N'104', CAST(31.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 6, N'104', CAST(15.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 1, 102)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 4, 104)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 6, 103)
GO

This is the data I doctored which is expected

USE [AdventureWorks2014]
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 1, N'102', CAST(18.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 4, N'102', CAST(31.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Recipe_RawMaterial] ([RecipeID], [RawMaterialID], [RevisionNumber], [PercentageInMix], [isIncluded]) VALUES (2, 6, N'102', CAST(15.00 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 1, 102)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 4, 102)
GO
INSERT [dbo].[RecipeRevisionNumber] ([RecipeID], [RawMaterialID], [RevisionNumber]) VALUES (2, 6, 102)
GO

---

If you think it you can achieve it

Answer 3

This is the result I was expecting In the Recipe_RawMaterials after the First update

And in the RecipeRevisionNumber table

---

If you think it you can achieve it

Answer 4

I am having a problem with my my After Insert and After Update triggers. When I insert a new record into the Recipe_rawMaterial table I want to check If that RecipeID and RawMaterialID exists in the RecipeRevisionNumner Table and get the RevisionNumber from that table and add it to the Recipe_RawMaterial table. If it does not exist in the RecipeRevisionNumber table create e new record With 100 as the RevisionNumber

Please explain your requirements and entity relationship model here. Cause this makes no sense for different reasons:

1) This structure currently makes it mandatory, that the consumer modifiys a recipe with amaterials in a single INSERT or UPDATE statement. This is as real world scenario pretty uncommon.

2) Revision in recipe and materials is redundant, and will lead to ambiguous data.

3) Your conditional in the UPDATE trigger is, while not being a tautology, seems to be non sense:

CREATE TRIGGER [dbo].[UpdatedRecipeRawMaterial]
ON [dbo].[Recipe_RawMaterial] 
AFTER UPDATE
AS 
IF EXISTS (
    SELECT * FROM INSERTED I 
	JOIN DELETED D ON D.RecipeID = I.RecipeID
             
    )
-- [..]

Cause when INSERTED is empty, DELETED is also. Thus simply JOINing them is sufficient. But why creating new revision numbers, when nothing has changed? btw, it can be shortend, cause the second NOT EXISTS() is a tautology, if that branch is reached.

The two most common scenarios:

1) A recipe as a version/revision. When ever it is changed, the revision is changed. Thus you only need a revision in the recipe table.

2) Having a "revision" column in the recipe's material table means normally that you need a history of the recipe. Cause when being valid, then the information in that column would be: "material was added/modified for revision x". But this does not requires a different approach for normal real world scenarios.

Answer 5

1) A recipe as a version/revision. When ever it is changed, the revision is changed. Thus you only need a revision in the recipe table.

This is the scenario that i am looking for Whenever a part of the recipe is changed the revisionnumber should be increamented

I have also revised the update statement above so that even when the RawMaterial changes the RevisionNumber will be updated

UPDATE RecipeRevisionNumber SET RevisionNumber= RecipeRevisionNumber.RevisionNumber + 1 FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID and MyRecipeRawMaterial.RawMaterialID=I.RawMaterialID 
INNER JOIN Deleted D ON MyRecipeRawMaterial.RecipeID = D.RecipeID and MyRecipeRawMaterial.RawMaterialID=D.RawMaterialID 

I understand what you are saying about the if not exist part of the After Update Trigger I have removed that part - thank you

USE [AdventureWorks2014]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[UpdatedRecipeRawMaterial]
ON [dbo].[Recipe_RawMaterial] 
AFTER UPDATE
AS 
IF EXISTS (
    SELECT * FROM INSERTED I 
	JOIN DELETED D ON D.RecipeID = I.RecipeID
             
    )

BEGIN 

UPDATE RecipeRevisionNumber SET RevisionNumber= RecipeRevisionNumber.RevisionNumber + 1 FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID  
INNER JOIN Deleted D ON MyRecipeRawMaterial.RecipeID = D.RecipeID  


DECLARE @MyRevisionNumber int=0
SELECT @MyRevisionNumber= (SELECT RecipeRevisionNumber.RevisionNumber 
							FROM RecipeRevisionNumber INNER JOIN Inserted I 
							ON RecipeRevisionNumber.RecipeID = I.RecipeID and RecipeRevisionNumber.RawMaterialID=I.RawMaterialID  
							)


UPDATE Recipe_RawMaterial SET Recipe_RawMaterial.RevisionNumber = @MyRevisionNumber 
FROM [dbo].[Recipe_RawMaterial] MyRecipeRawMaterial 
INNER JOIN Inserted I ON MyRecipeRawMaterial.RecipeID = I.RecipeID WHERE MyRecipeRawMaterial.RecipeID=I.RecipeID

END

---

If you think it you can achieve it

Answer 6

In this case it's pretty simple:

USE tempdb;
GO

CREATE TABLE dbo.Recipe (
    RecipeID INT NOT NULL ,
    RecipeName NVARCHAR(255) NOT NULL ,
    RevisionNumber INT NOT NULL
        CONSTRAINT DF_Recipe_RevisionNumber
            DEFAULT ( 0 ) ,
    CONSTRAINT PK_Recipe
        PRIMARY KEY CLUSTERED ( RecipeID ASC )
);

CREATE TABLE dbo.Material (
    MaterialID INT NOT NULL ,
    MaterialName NVARCHAR(255) NOT NULL ,
    CONSTRAINT PK_Material
        PRIMARY KEY CLUSTERED ( MaterialID ASC )
);

CREATE TABLE dbo.Formula (
    RecipeID INT NOT NULL ,
    MaterialID INT NOT NULL ,
    AmountNumber NUMERIC(5, 4) NOT NULL ,
    CONSTRAINT PK_Formula
        PRIMARY KEY CLUSTERED (
            RecipeID ASC ,
            MaterialID ASC ) ,
    CONSTRAINT FK_Formula_Material
        FOREIGN KEY ( MaterialID )
        REFERENCES dbo.Material ( MaterialID ) ,
    CONSTRAINT FK_Formula_Recipe
        FOREIGN KEY ( RecipeID )
        REFERENCES dbo.Recipe ( RecipeID )
);
GO

CREATE TRIGGER tr_Formula_IU
ON dbo.Formula
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;

UPDATE R
SET    R.RevisionNumber += 1
FROM   dbo.Recipe R
       INNER JOIN INSERTED I ON I.RecipeID = R.RecipeID;
GO

INSERT INTO dbo.Recipe ( RecipeID ,
                         RecipeName )
VALUES ( 1, N'R1' ) ,
       ( 2, N'R2' ) ,
       ( 3, N'R3' );

INSERT INTO dbo.Material ( MaterialID ,
                           MaterialName )
VALUES ( 1, N'M1' ) ,
       ( 2, N'M2' ) ,
       ( 3, N'M3' );

-- Single step operations.
INSERT INTO dbo.Formula ( RecipeID ,
                          MaterialID ,
                          AmountNumber )
VALUES ( 1, 1, 0.5 ) ,
       ( 1, 2, 0.5 ) ,
       ( 2, 2, 1 );

SELECT R.RecipeID ,
       R.RecipeName ,
       R.RevisionNumber ,
       F.AmountNumber ,
       M.MaterialName
FROM   dbo.Recipe R
       LEFT JOIN dbo.Formula F ON F.RecipeID = R.RecipeID
       LEFT JOIN dbo.Material M ON M.MaterialID = F.MaterialID;


-- Two step operation will lead to increase of revision by two.
INSERT INTO dbo.Formula ( RecipeID ,
                          MaterialID ,
                          AmountNumber )
VALUES ( 1, 3, 1 );
UPDATE dbo.Formula
SET    AmountNumber = 1 / 3.0
WHERE  RecipeID = 1;

SELECT R.RecipeID ,
       R.RecipeName ,
       R.RevisionNumber ,
       F.AmountNumber ,
       M.MaterialName
FROM   dbo.Recipe R
       LEFT JOIN dbo.Formula F ON F.RecipeID = R.RecipeID
       LEFT JOIN dbo.Material M ON M.MaterialID = F.MaterialID;
GO

DROP TABLE dbo.Formula;
DROP TABLE dbo.Material;
DROP TABLE dbo.Recipe;
GO