For one of the project I am required to record auditing for all the additions and updates. The version of SQL Server in use is SQL Server 2005 Standard. There is no possibility to upgrade it to SQL Server 2008 R2 Enterprise Edition (So no option to use Change Data Capture Feature). Now, I am left with only one option i.e., to use triggers after insert and after update.
My concern is the performance of the database system as there will be tens of tables involved in multiple users writing to database and periodic and overnight jobs will write to database as well.
Can you please suggest some possible solution to the issue. Is there only one option to use trigger to achieve the purpose or there are some alternatives to it. Your advice, suggestions are appreciated.