Another aspect that is often overlooked (maybe because it's not a requirement for a majority of people...) is auditing SELECT statements.  Not necessarily an easy task, but I have run into read/SELECT auditing requirements in the past.  But anyway...

Arun Marathe gives some very good info on query plan recompilations at http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.  This information is quoted in part from that article:

Trigger recompilations happen when SQL Server determines that a trigger execution will result in the 'inserted' or 'deleted' tables having "significantly different" (or sufficiently different) rowcounts.  In SQL 2000, a "significant difference" is determined by the formula:

ABS(log₁₀(n+5) - log₁₀(m+5)) >= 1

Where n is the rowcount of the 'inserted' or 'deleted' table in the cached query plan, and m is the rowcount for the current trigger execution.

For SQL 2005, the formula was changed slightly.  If you're going to end up with more rows than are in the cached query plan after trigger execution (m > n):

ABS(log₁₀(n) - log₁₀(m)) > 1

If you'll end up with the same or less rows after trigger execution (m <= n):

ABS(log₁₀(n) - log₁₀(m)) > 2.1

So, for SQL 2000, if you have a cached trigger query execution plan with a rowcount of 100, the query will (theoretically) remain cached until you fire an instance of the trigger that generates a rowcount between 0 and 10 or 1,045+.

For SQL 2005, a cached trigger query execution plan with a rowcount of 100 will remain cached until you fire an instance of the trigger that generates a rowcount of 1,001 or more.

At the opposite end of the scale, on SQL 2005, if your cached trigger query execution plan has a rowcount of 1,000, it will remain cached until you fire an instance of the trigger that generates a rowcount of 7 or less.