Welcome to the forum.
The term "useful" can mean different things to different people.
Here are some of the 3rd party Transition plug-ins, that will work (in their current versions) in CS4:
HollywoodFX used to work, with a bit of file renaming, but I think that Pinnacle has dropped that line of Transitions
Red Giant has a host of effects plug-ins that work both in AE and in Premiere. Magic Bullet Looks being the most important of those. Personally, I find this plug-in so powerful, I won't edit without it.