propharma
asked on
Is a Trigger appropriate for the following scenario, or do I need to try something else?
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It depends on the existence of your define triggers type. if they are of type 'for insert' it should be run when data inserted into table so if you need to disable trigger then first disable trigger and then insert data.
ASKER
So perhaps a stored proceedure that can be executed from any DB then?
ASKER
Very good explanation of the problem as well as an alternate solution to the core issue. Thanks.
if the query effect only 1 row of the table with trigger, there will be 1 fire of trigger with that 1 row
if the query effect 100 rows of that table, there will be 1 fire of trigger with 100 rows