How to create a trigger in SQL Server that insert values into two tables using an ID stored in the second one

Question

I have three tables:

RawValues that contains Timestamp, Part, PartNumber,ValueKind, ... , Value
Measurements that contains TimeStamp, ID, Value.
Hashtable that contains ID, Part, ValueKind, ...

I have a stream of data that come into RawValue that I cannot modify. It contains all the columns except the ID.

I want to create a trigger on RawValues that insert the values into Hashtable (if they do not yet exist), select the id from Hashtable and then insert the row into Measurements.

This is what I've created:

CREATE TRIGGER [dbo].[TrigInsertToMeasurements]
ON [dbo].[MeasurementsTemp]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

       -- Dichiaro le variaibili --
       DECLARE 
             @TimeStamp datetime, 
             @MachineType char(10), 
             @MachineModel char(10), 
             @MachineNumber int, 
             @Part char(10),
             @PartNumber int,
             @ValueKind char(10),
             @Value float,
             @ID     int

       -- Assegno i valori --
       SELECT 
             @TimeStamp = TimeStamp,
             @MachineType = MachineType,
             @MachineModel = MachineModel,
             @MachineNumber = MachineNumber, 
             @Part = Part,
             @PartNumber = PartNumber,
             @ValueKind = ValueKind,
             @Value = Value
       FROM
             inserted


        SELECT @ID=ID FROM [dbo].[Hashtable]
        WHERE 
             @MachineType = [MachineType] AND
             @MachineModel = [MachineModel] AND
             @MachineNumber = MachineNumber AND
             @Part = Part AND
             @PartNumber = PartNumber AND
             @ValueKind = ValueKind


        IF count(@ID) = count(@TimeStamp)
        BEGIN
            INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] ) VALUES (@TimeStamp, @ID, @Value)
        END
        ELSE BEGIN

            BEGIN
            IF NOT EXISTS (SELECT [ID] FROM [dbo].[Hashtable] WHERE
             @MachineType = [MachineType] AND
             @MachineModel = [MachineModel] AND
             @MachineNumber = MachineNumber AND
             @Part = Part AND
             @PartNumber = PartNumber AND
             @ValueKind = ValueKind)
            BEGIN
            INSERT INTO [dbo].[Hashtable] (MachineType, MachineModel, MachineNumber, Part, PartNumber, ValueKind)
            VALUES (@MachineType, @MachineModel, @MachineNumber, @Part, @PartNumber, @ValueKind)
            END
            END         

            SELECT @ID=ID FROM [dbo].[Hashtable]
            WHERE 
             @MachineType = MachineType AND
             @MachineModel = MachineModel AND 
             @MachineNumber = MachineNumber AND
             @Part = Part AND
             @PartNumber = PartNumber AND
             @ValueKind = ValueKind

             INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] ) VALUES (@TimeStamp, @ID, @Value)
        END
END

GO

ALTER TABLE [dbo].[MeasurementsTemp] ENABLE TRIGGER [TrigInsertToMeasurements]
GO

The trigger works correctly but sometime in the stream comes rows in groups, then in this case not all the rows are considered. What can I do? Maybe use a for loop and then access to the arrays? How to do it?

your concept of trigger is wrong. Trigger occurs not for each record. the INSERTED may contains more than 1 row. You need to handle that in your trigger — Squirrel
– Squirrel, Commented Feb 24, 2016 at 10:16
Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your INSERT statements affects 25 rows, you'll get the trigger fired once, but then Inserted will contain 25 rows. You cannot just simply do a SELECT .... FROM Inserted - you'll get only one arbitrary row and ignore all others - typically not what you want to do. You need to completely rewrite your trigger in a set-based fashion — marc_s
– marc_s, Commented Feb 24, 2016 at 10:25
Possible duplicate of Handling multiple records in a MS SQL trigger — Tab Alleman
– Tab Alleman, Commented Feb 24, 2016 at 13:50

Squirrel · Accepted Answer · 2016-02-24 10:30:42Z

i try to re-code your trigger, replacing it accordinlgy best to my understanding of the logic

CREATE TRIGGER [dbo].[TrigInsertToMeasurements]
ON [dbo].[MeasurementsTemp]
FOR INSERT
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;

       -- Dichiaro le variaibili --
       DECLARE 
             @TimeStamp datetime, 
             @MachineType char(10), 
             @MachineModel char(10), 
             @MachineNumber int, 
             @Part char(10),
             @PartNumber int,
             @ValueKind char(10),
             @Value float,
             @ID     int

       -- Assegno i valori --
       --SELECT 
       --      @TimeStamp = TimeStamp,
       --      @MachineType = MachineType,
       --      @MachineModel = MachineModel,
       --      @MachineNumber = MachineNumber, 
       --      @Part = Part,
       --      @PartNumber = PartNumber,
       --      @ValueKind = ValueKind,
       --      @Value = Value
       --FROM
       --      inserted


        --SELECT @ID=ID FROM [dbo].[Hashtable]
        --WHERE 
        --     @MachineType = [MachineType] AND
        --     @MachineModel = [MachineModel] AND
        --     @MachineNumber = MachineNumber AND
        --     @Part = Part AND
        --     @PartNumber = PartNumber AND
        --     @ValueKind = ValueKind


        IF count(@ID) = count(@TimeStamp)
        BEGIN
            --INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] ) VALUES (@TimeStamp, @ID, @Value)
        INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] ) 
        SELECT  i.TimeStamp, h.ID, i.Value
        FROM    inserted i
            INNER JOIN [dbo].[Hashtable] h  ON  i.[MachineType]     = h.[MachineType]
                            AND i.[MachineModel]    = h.[MachineModel]
                            AND i.MachineNumber     = h.MachineNumber
                            AND i.Part          = h.Part
                            AND i.PartNumber        = h.PartNumber
                            AND i.ValueKind     = h.ValueKind
        WHERE   i.TimeStamp IS NOT NULL

        END
        ELSE 
    BEGIN

      --      BEGIN
            --IF NOT EXISTS (SELECT [ID] FROM [dbo].[Hashtable] WHERE
            -- @MachineType = [MachineType] AND
            -- @MachineModel = [MachineModel] AND
            -- @MachineNumber = MachineNumber AND
            -- @Part = Part AND
            -- @PartNumber = PartNumber AND
            -- @ValueKind = ValueKind)
               -- BEGIN
               -- INSERT INTO [dbo].[Hashtable] (MachineType, MachineModel, MachineNumber, Part, PartNumber, ValueKind)
               -- VALUES (@MachineType, @MachineModel, @MachineNumber, @Part, @PartNumber, @ValueKind)
               -- END
      --      END    
           INSERT INTO [dbo].[Hashtable] (MachineType, MachineModel, MachineNumber, Part, PartNumber, ValueKind)
       SELECT   i.MachineType, i.MachineModel, i.MachineNumber, i.Part, i.PartNumber, i.ValueKind
       FROM     inserted i
       WHERE    NOT EXISTS
            (
                SELECT  *
                FROM    [dbo].[Hashtable] x
                WHERE   x.MachineType   = i.MachineType
                AND x.MachineModel  = i.MachineModel
                AND x.MachineNumber = i.MachineNumber
                AND x.Part      = i.Part
                AND x.PartNumber    = i.PartNumber
                AND x.ValueKind = i.ValueKind
            )

            --SELECT @ID=ID FROM [dbo].[Hashtable]
            --WHERE 
            -- @MachineType = MachineType AND
            -- @MachineModel = MachineModel AND 
            -- @MachineNumber = MachineNumber AND
            -- @Part = Part AND
            -- @PartNumber = PartNumber AND
            -- @ValueKind = ValueKind

            -- INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] ) VALUES (@TimeStamp, @ID, @Value)
        INSERT INTO [dbo].[Measurements] ([TimeStamp], [ID], [Value] )
        SELECT  i.[TimeStamp], h.ID, i.Value
        FROM    inserted i
            INNER JOIN [dbo].[Hashtable] h  ON  i.MachineType   = h.MachineType
                            AND i.MachineModel  = h.MachineModel
                            AND i.MachineNumber = h.MachineNumber
                            AND i.Part      = h.Part
                            AND i.PartNumber    = h.PartNumber
                            AND i.ValueKind = h.ValueKind
        END
END

Collectives™ on Stack Overflow

How to create a trigger in SQL Server that insert values into two tables using an ID stored in the second one

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related