No, the problem is not the code that makes the update. The trigger objects will recompile regardless of what object is doing the update.

From our point of view:

SQL server have three caching objects for triggers

Single row trigger (setopts & 1024 = 1024)

2-4 row trigger

5- row trigger (same caching "space" as 2-4 row trigger)

The problem arises because two objects share the space in the cache and hence forces removal of the other object. The second problem is that when recompiling, all objects are removed. Only the object with the correct setopts should be removed. The single row trigger should NOT be removed, when a multi row trigger needs to be recompiled.

Leif Haraldsson