Triggers are a requirement for any complex data integrity rules. These cannot be enforced anywhere except the database or you will have data integrity problems.
They are also the best place for auditing unless you don't want to capture all changes to the database (which is the problem of auditing from the application).
Triggers can cause performance issues if not written carefully and not enough developers are knowledgeable enough to write them well. This is part of where they get their bad rap.
Triggers are often slower than other means of maintaining data integrity, so if you can use a check constraint, use that instead of a trigger.
It is easy to write bad triggers that do stupid things like try to send emails. Do you really want to be unable to change records in the db if the email server goes down?
In SQL server, triggers operate on a batch of records. All too often developers think they only need to handle one record inserts, updates or deletes. That is not the only kind of data changes that happen to a database and all triggers should be tested under the conditions of 1 record change and many record changes. Forgetting to do the second test can lead to extremely poorly performing triggers or a loss of data integrity.