I have a table [Latest_Entry_Value] like this, which store latest data of each entry and get update every 10 min or so.

and I need a trigger to update or insert this value to table [Entry_Value_Daily_Log], which store last value of the day of each entry like this

I have try an update trigger on [Latest_Entry_Value] with if condition to do update or insert into [Entry_Value_Daily_Log] which work fine if I'm update [Latest_Entry_Value] one row at a time.

CREATE TRIGGER [dbo].[Update_Entry_Value_Daily_Log] 
   ON  [dbo].[Latest_Entry_Value] 
   AFTER UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   DECLARE @Entry_Name VARCHAR(20);
   DECLARE @Entry_Group VARCHAR(20);
   DECLARE @Value INT;
   
   SELECT 
      @Entry_Name = Entry_Name,
      @Entry_Group = Entry_Group,
      @Value = Value
   FROM INSERTED;

   -- Check [Entry_Value_Daily_Log] if entry log date already exist
   IF EXISTS(SELECT * FROM [dbo].[Entry_Value_Daily_Log] WHERE [Entry_Name] = @Entry_Name AND [Date] = CAST(GETDATE() AS DATE))
   BEGIN
      -- row existed, do update
      UPDATE [dbo].[Entry_Value_Daily_Log]
      SET [Value] = @Value
      WHERE [Entry_Name] = @Entry_Name AND [Date] = CAST(GETDATE() AS DATE)
   END
   ELSE
   BEGIN
      -- new day, do insert
      INSERT INTO [dbo].[Entry_Value_Daily_Log]
      ([Entry_Name],[Entry_Group],[Date],[Value])
      VALUES
      (@Entry_Name, @Entry_Group, CAST(GETDATE() AS DATE), @Value)
   END
ENDฺ

But when I do batch update to [Latest_Entry_Value] like

UPDATE [dbo].[Latest_Entry_Value]
SET [Value] = [Value]   2
WHERE [Entry_Group] = 1

which should effect 2 row in [Latest_Entry_Value]. only the first row got triggered and update or insert to [Entry_Value_Daily_Log]. Which, from my understanding after google, happen because trigger is suppose to be only one transaction. so how should I write the trigger to solve this.

CodePudding user response：

A trigger will fire only once when more than one row is updated at once, not one time for each updated row.
So all you need to do in your trigger is update all matching rows, and insert all others.
You can do that direct by joining on the inserted table.
See the example below, you might need to adjust some things but it should get you in the right direction.

CREATE TRIGGER [dbo].[Update_Entry_Value_Daily_Log] 
   ON  [dbo].[Latest_Entry_Value] 
   AFTER UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   declare @date date = getdate();

   -- update existing
   UPDATE l
   SET    l.Value = i.Value
   from   dbo.Entry_Value_Daily_Log l
     inner join inserted i on l.Entry_Name = i.Entry_Name
                          and l.Entry_Group = i.Entry_Group
                          and i.Date = @date


   -- new day, do insert
   INSERT INTO [dbo].[Entry_Value_Daily_Log]
     (Entry_Name, Entry_Group, Date, Value)
   select i.Entry_Name, i.Entry_Group, i.Date, i.Value
   from inserted i
   where not exists (select 1 
                     from   dbo.Entry_Value_Daily_Log l 
                     where  i.Entry_Name = l.Entry_Name
                     and    i.Entry_Group = l.Entry_Group
                     and    i.Date = @date
                    )

END

You could also use Merge in stead of doing update and insert