locked
After Insert and After Update Trigger problem RRS feed

  • 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
    

    • Edited by tendaimare Wednesday, January 16, 2019 6:31 AM
    Wednesday, January 16, 2019 6:26 AM

Answers

  • 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
    

    • Marked as answer by tendaimare Wednesday, January 16, 2019 11:59 AM
    Wednesday, January 16, 2019 11:36 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Wednesday, January 16, 2019 8:17 AM
  • 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

    Wednesday, January 16, 2019 9:09 AM
  • 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

    Wednesday, January 16, 2019 9:10 AM
  • 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.

    Wednesday, January 16, 2019 9:53 AM
  • 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

    Wednesday, January 16, 2019 10:20 AM
  • 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
    

    • Marked as answer by tendaimare Wednesday, January 16, 2019 11:59 AM
    Wednesday, January 16, 2019 11:36 AM