Обновить поле в одной таблице по совокупному значению других полей в другой таблице

create trigger dbo.Update_Recipes_Consistency
on dbo.RecipeIngredients
for insert, update, delete
as
begin
 set nocount on;
 
 with s as
 (
  select RecipeID from inserted
  union
  select RecipeID from deleted
 )
 update r
  set
   Consistent = c.Consistent,
   DateTimeUpdated = getdate()
 from
  s join
  dbo.Recipes r on r.RecipeID = s.RecipeID outer apply
  (
   select
    sum(case when WeigherID = 1 then Quantity end),
    sum(case when WeigherID = 2 then Quantity end),
    sum(case when WeigherID = 3 then Quantity end)
   from
    dbo.RecipeIngredients
   where
    RecipeID = s.RecipeID and WeigherID in (1, 2, 3)
  ) ri(BatchSize1, BatchSize2, BatchSizeMicro) cross apply
  (select cast(case when r.BatchSize1 = ri.BatchSize1 and r.BatchSize2 = ri.BatchSize2 and r.BatchSizeMicro = ri.BatchSizeMicro then 1 else 0 end as bit)) c(Consistent)
 where
  r.Consistent <> c.Consistent;
 
end;

CREATE VIEW vRecipeConsistency
WITH SCHEMABINDING
AS
SELECT
   RecipeID,
 
/*вычисляемое поле Consistency*/ AS Consistent
 
FROM
   dbo.RecipeIngredients
 
ORDER BY RecipeID
 
GO
 
CREATE UNIQUE CLUSTERED INDEX IX_vRecipeConsistency__RecipeID on dbo.vRecipeConsistency (RecipeID);

create view dbo.vRecipeConsistency
with schemabinding
as
select
 r.RecipeID,
 count_big(*) as cnt,
 cast(case
  when r.BatchSize1 = sum(case when WeigherID = 1 then Quantity end) and
       r.BatchSize2 = sum(case when WeigherID = 2 then Quantity end) and
       r.BatchSizeMicro = sum(case when WeigherID = 3 then Quantity end) then 1
  else 0
 end as bit) as Consistent
from
 dbo.Recipe r join
 dbo.RecipeIngredients ri on ri.RecipeID = r.RecipeID
where
 ri.WeigherID in (1, 2, 3)
group by
 r.RecipeID,
 r.BatchSize1,
 r.BatchSize2,
 r.BatchSizeMicro;
go
 
create unique clustered index IX_vRecipeConsistency__RecipeID on dbo.vRecipeConsistency (RecipeID);
go

sum(case when WeigherID = 1 then Quantity end)

r.BatchSize1 = sum(case when WeigherID = 1 then Quantity end)

(r.BatchSize1 = sum(case when WeigherID = 1 then Quantity end) or (r.BatchSize1 is null))

isnull(r.BatchSize1, -1) = isnull(sum(case when WeigherID = 1 then Quantity end, -1)

create view dbo.vRecipeBatchSizes
with schemabinding
as
select
 ri.RecipeID,
 count_big(*) as cnt,
 sum(case when WeigherID = 1 then Quantity end) as BatchSize1,
 sum(case when WeigherID = 2 then Quantity end) as BatchSize2,
 sum(case when WeigherID = 3 then Quantity end) as BatchSizeMicro
from
 dbo.RecipeIngredients ri
where
 ri.WeigherID in (1, 2, 3)
group by
 ri.RecipeID;
go
 
create unique clustered index IX_vRecipeConsistency__RecipeID on dbo.vRecipeBatchSizes (RecipeID);
go
 
create view dbo.vRecipeConsistency
as
select
 r.RecipeID,
 cast(case
  when isnull(r.BatchSize1, -1) = isnull(s.BatchSize1, -1) and
       isnull(r.BatchSize2, -1) = isnull(s.BatchSize2, -1) and
       isnull(r.BatchSizeMicro, -1) = isnull(s.BatchSizeMicro, -1) then 1
  else 0
 end as bit) as Consistent
from
 dbo.Recipe r join
 dbo.vRecipeBatchSizes s with (noexpand) on s..RecipeID = r.RecipeID;
go