We currently have a trigger set up on a table to audit any data changes similar to this answer, which uses dynamic SQL to generate the audit rows. In order to use the
DELETED tables in the dynamic SQL they have to be inserted into temporary tables.
We are currently trying to limit our
tempdb usage as it has been causing issues and this table is udpated frequently, and is often causing
tempdb contention as the trigger is creating and dropping so many temporary tables.
Is there a way to audit data changes to this table without using temporary tables? Obviously I can hard code in the columns myself into the trigger and audit tables, but I'd rather have a solution which doesn't need updating every time there is a schema change on the table.
I have been looking around and I expect the answer to be 'No you can't do this' but I thought it was worth an ask in case I was missing some obscure method (or newer feature as we are on SQL Server 2016). As you can guess the solution needs to have good performance, otherwise it will cause issues somewhere else!