Something that has not been mentioned in all of this is the method by which applications are connecting to the SQL Server. If they are connecting through direct, trusted connections, then the trigger will have access to the username making the DML statement and auditing via the trigger will work fine. However, if applications connect through an abstraction layer such as COM+ running under its own user or a website, then the trigger will only have access to that middle-tier username (IUSR..., your COM+ user etc.) and it will make the audit data meaningless. In this last scenario, you have to pass the username from the presentation tier down to the database and in that scenario stored procedures make more sense.

I generally try to avoid triggers when I can. The ugliest part of triggers is that they fire for everyone all the time which can be a pain in administrative situations. If auditing is really the driving force and if you have access to the end user connecting to SQL (which presumably you would if triggers were an option) then I would recommend finding a program that monitors the DB log directly. That eliminates the use of triggers and provides your audit trail.