How to send update batch with statements simultaneously in SQL

Question

BEGIN TRANSACTION 

    UPDATE table_x
    SET Part = 8 
    WHERE A = 2 AND B = 2 AND C = 1 AND Code = 'X'

    UPDATE table_x
    SET Part = 0 
    WHERE A = 2 AND B = 2 AND C = 1 AND Code = 'Y'

    UPDATE table_x
    SET Part = 2 
    WHERE A = 2 AND B = 2 AND C = 1 AND Code = 'Z'

    COMMIT TRANSACTION

Basically we have three rows that together has to have a value total of 10 (as you can see now it is 8+0+2 = 10). I have a trigger that checks so the value always should be 10 when updating or inserting new values, but as for now it wont be able to update since it sends the first UPDATE "WHERE A = 2 AND B = 2 AND C = 1 AND Code = 'X' " to check, and the sum will in most cases not go through since it will be either more or less than 10.

What I want is to send all these UPDATES at the same time so the values changes together. Is there any way to make that happen?

Answer 1

SQL Server is actually a bit smarter about triggering, so one statement should work:

UPDATE table_x
  SET Part = (CASE Code WHEN 'X' THEN 8 WHEN 'Y' THEN 0 ELSE 2 END)
  WHERE A = 2 AND B = 2 AND C = 1 AND Code IN ('X', 'Y', 'Z');

The trigger itself has to be really smart about checking inserted values rather than values in the table, but this is doable.

Alternatively (and possibly the better solution under some circumstances) is to disable the trigger for the updates. This has the advantage that the trigger code does not need to change.