There are cases where every access has to be audited (legal requirement). It's impossible to do this auditing by using triggers, since we don't have ON SELECT triggers. So if auditing is done that way in these cases, that's illegal bevause the legal requirement isn't being met.

There are cases where auditing is required only on changes (update, insert, delete) and in this case it may be possible to do it by trigger. If the audit requirement includes identification of who invoked the change it isn't possible to do it by trigger unless knowing the database principla on behalf of whom each operation is executed implies knowing the end-user who initiated that action, so once again doing it by triggers might not enable the required auditing, so if the required auditing is a legal requirement doing the audit be triggers will usually (because databases usually don't have a separate database principal for each end user of each application that uses the data) be illegal just as in the case where all access is required to be audited.

If you require only to audit what was changed and and when but not who did it (exept that the database principal used can be recored, which is almost useless for identifying the "who" then it's possible to do auditing with triggers. Usuually any better identification of who did it and always any requirement to audit read access means you can't do it that way. One option is to do the auditing in the app, but it probably isn't a good option; another is to insist that the apps always call stored procedures (which is a very useful way of placing a nice modular boundary between app and db, so it's best practice anyway - I was somehat amazed to see a .net developer saying he would refuse to follow best practice) and have each SP to which the apps have access take a user-identity parameter indicating who the end-user was, and then the auditing can be done (or invoked) from those stored procedures. Those interface SPs are then the software wall Joe C referred to.