Solved

Is a Trigger appropriate for the following scenario, or do I need to try something else?

Posted on 2011-03-18
5
224 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:propharma
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35170311
the trigger will be fired for each query.

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
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35171358
just on general principles what you are suggesting is not a good idea...
and i would also suggest that it could not be guaranteed to function in future even if it appeared to work now...

how would the dbms work... if you dropped the index it was attempting to use to perform its access with?

the triggers fires each time a "set of rows" is affected....

which depends on the style of the originating sql  and how and where that is processed in the processing... remember that a table can have many triggers specified on it and you have little control over the order in which they are processed... also triggers can be "nested" and called recursively...

a better process is to code the drop and recreate of any indexes around the statement/process you are running... (e.g. a prior and succeeding job steps in a batch job...)  that would also lead to a simpler test and maintainence process.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 35171611
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.
0
 

Author Comment

by:propharma
ID: 35173034
So perhaps a stored proceedure that can be executed from any DB then?
0
 

Author Closing Comment

by:propharma
ID: 35487842
Very good explanation of the problem as well as an alternate solution to the core issue.  Thanks.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question