nav2000, the point is that with a Trigger, it will always 'fire' no matter how the table is accessed (bulk load may bypasses a trigger. I am not as familiar with them in the SQL Server environment). You may only know about stored procedures, someone 'forgets' to add the audit code (or the call to and audit process) or sometime in the future other methods than stored procedures may be used. With a Trigger you (nor the programmer) needs to worry about it.
On principle, data-oriented busieness logic should be kept as close to the data as possible. This means, in order of proximity:
4) Procedures and Views
5) Client-side code
If it's not practically useful, then it's practically useless.
Triggers can be used to enforce business rules that can't be enforced by a constraint. In that sense triggers are the next best thing to a constraint because as Blindman suggests they are closest to the data and less likely to be bypassed by other logic. True, triggers can be bypassed but then so can foreign key and check constraints.
In my experience it's best to avoid triggers for any other logic, especially any logic that modifies data. A trigger is just a procedure that is triggered by some other code. In most cases it makes sense to modify that other code rather than use a trigger. That's because triggers almost inevitably obfuscate code to some degree plus they are hard work to maintain - especially for DBAs doing data maintenance tasks.