As much as we hear about this “excellent new feature”, the fact remains that many developers won’t be patching their existing titles, which means we’ll have to wait some more in order to enjoy it. Among the latest who have decided on not patching their existing titles are Insomniac, Infinity Ward, 2K Games, Codemasters, etc.
So, I guess we can forget about trophies in Call of Duty 4 or Civilization Revolutions, as well as numerous other games currently on the market. EA and Naughty Dog, on the other hand, already jumped on the trophy patch wagon, so you can expect their support to come within a reasonable period.
You can view a nice detailed list of games supporting, and launching with trophies here.