My project is to audit 5 to 10 existing tables in our system without prolonging transactions. Whatever method is used, it has to work on SQL Server Express 2005 to (eventually) 2016.
I've done research on Change Data Capture (CDC) and Change Tracking. Change Tracking doesn't capture the specific changes, and CDC is only available in Enterprise Edition.
Then I stumbled upon Service Broker. I was intrigued by Service Broker, so I started creating a prototype. Service Broker is working fine, but answers received in two of my other posts lead me to believe that this might not be the right way to go. Too complicated for nothing. I'm still in the analysis stage and trying different things as part of my analysis.
Right now the results of the service broker are not convincing... a bulk update of 105000 items to a price table, takes 38 seconds while the processing of the Queue (the auditing part) takes 17 seconds... but the 38 seconds includes the double processing of inserting into 2 #temp tables that are then used to insert into the TMPins and TMPdel. So I figure that I can cut that in half... I'm now questioning the use of the service broker... logically the trigger would probably take the same amount of time just by inserting the info straight into the audit table...
To clarify, when I say Bulk insert, It Isn't the "Bulk Insert" Function. I'm talking about a big chunk of data that is inserted or updated at once. when updating 105000 items in the price table, I want to audit the changes that happened. When I say changes that happen, I decided to insert the new values in the audit table (if it's an insert or update) or insert the primary key with all other fields null (for records that have been deleted)... So yes! it can be after the data is loaded in but I don't want to lose any audit (I don't want a transactions to pass out of order)
The two other posts will help get the context of what I'm trying to do and what I've tried:
- Trigger to create a variable Table to be sent to service broker
- triggers - using the inserted / deleted tables in Dynamic SQL
I value every idea.