Executing a trigger?

问题:

I want to fire the trigger when I insert multiple values and even If they duplicated like this:

Assuming that the values were 0 (Table_1):

Inserting the values above (while having the trigger created) should give us:

It works fine however, If the value of bought is 99 for "Prod1" for example and I run:

Regarding Prod2, Its not updating at all for the simple reason because Prod1 had a constraint error.

回答1:

You could use IIF or a CASE to cap to the limit you need:

UPDATE T1
SET Bought = IIF((Bought + uc.UpdCount) < 100, Bought + uc.UpdCount, 100)
FROM TABLE_1 T1
JOIN (
    SELECT ProdName, COUNT(*) as UpdCount 
    FROM Inserted 
    GROUP BY ProdName
) uc ON uc.Name = T1.ProdName
WHERE T1.Bought < 100;

Using a MERGE statement to upsert, then it would look something like this:

MERGE TABLE_1 AS t  
USING (
    SELECT ProdName, COUNT(*) as UpdCount 
    FROM Inserted 
    GROUP BY ProdName
) AS s (Name, UpdCount) ON (t.ProdName = s.Name)  
WHEN MATCHED THEN   
  UPDATE SET Bought = IIF((t.Bought + s.UpdCount) < 100, t.Bought + s.UpdCount, 100)
WHEN NOT MATCHED THEN  
  INSERT (ProdName, Bought) VALUES (s.Name, IIF(s.UpdCount < 100, s.UpdCount, 100));