I agree with avoiding them by proper design, with one exception: generating audit files; I don't want to trust the application layer to do that.

The other time I've found them indispensible is when dealing with 3rd party software, either to add functionality, or to fix the vendor's mistakes. Just don't tell the vendor, they'll blame the triggers for all their application bugs!