Triggers can do many things and are controlled by the driver they're attached to.
The SD controller has a trigger so that every time it reads or writes it turns the LED on, something which would be impossible with a userland daemon. Similarly, you can have set the trigger for the LEDs to "phyXrx" and "phyXtx" to blink every time data is received or sent, or the phyXassoc trigger which comes on only when the wifi is actually connected (but no indication whether it is turned on or not), all controlled by the kernel module itself.
There's triggers for the charging module and the fuel gauge, for bluetooth, for any regular GPIO line, plus a few basic things to simply turn them on, or blink at regular intervals. I know there's also a trigger for CPU activity but I don't think it's part of the Pandora's kernel.