Thank you for the suggestions.

We have gone through the syscacheobjects table. Note that the setopts values are not completely documented for SQL 2000.

And this problem arises because a trigger has three possible caching objects, but setopts can only use two. A multirow trigger har no special setopts, and a single row trigger has setopts & 1024 = 1024. This is clear.

The only time recompile occurs is when the two multi row trigger objects compete to get the one caching object for the trigger.

See my example.

Only updating the table with single row updates, does not cause any recompile. Adding updates of ten rows does not cause any recompile. But when a three row update occurs, then all objects are removed from cache and then recompiled. If you would then mix a three row update and a ten row update, you would get recompiles "all the time". How SQL server determines this difference between 2-4 row update versus 5 row or more, is not known to us, but must be somewhere (hidden) on the caching object.

As far as we can understand, ANSI settings does not affect this behaviour.

Leif haraldsson