This is always an interesting topic for any Force.com developer. The official line is here, which is already a lot to digest. On top of that, there are additional scenarios that can complicate things. For instance, what happens when a lead is converted? Up to 4 objects can be involved and multiple triggering moments are there. I haven't seen an official document on that, but here's a helpful thread.
Now I also have something to add about what workflows, which I just realized recently. My scenario is like this:
- I need to do the email in the workflow so I won't hit on the 1000 email Mass sending quota
- I can't run the workflow off lead, as we're way over the quota for active rules
So I came up with a Contact workflow rule, for insert only. That turned out, to do me no good, because it never fired. All I saw in the log was
15:17:53.110 (5110976000)|WF_CRITERIA_BEGIN|.........ON_CREATE_ONLY 15:17:53.110 (5110982000)|WF_RULE_NOT_EVALUATED
From what I can tell, it looks like once the workflow rules get their chance, it's after all triggers are run. By then the Contact had already gone through a round of updating, which made it no longer considered "On create". When I changed the workflow rule to onCreateOrTriggeringUpdate, and things started to work.