We have a situation developing where we need to make use of several non-clustered indexes on several key tables in order to generate reports in a reasonable time frame. The problem is these are appended bulk-transaction databases which get updated with new records weekly. There is one data set for each client and we have several, often two to three per server.
Is it effective to use a Trigger to disable and then rebuild the indexes before and then after the INSERT, UPDATE, or DELETE statements are completed?
I may be misunderstanding the principle of a trigger - does the insertion of each row trigger the trigger? Or does it trigger once during the opening of the operation, and then close when the operation completes?