Ahhh auditing...

I've used mostly trigger based auditing through the years on stuff I've designed, although a few clients did do auditing in the data layer, which worked really well for them.

Based on the application architecture and auditing requriements it was a good approach for them.

DDL triggers are great for capturing DDL changes. I've used them in environment where security has been less than optimal due to company policies, but thats another discussion.