What are the best practices for triggers to maintain a revision number on records?
Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.