Bonnie,

Thanks. I guesss I will back off on this idea of turning it off. Actually, all of the triggers in the system have a test in them:
if cosyst.lexcl = 1
return

Batch operations such as period end close set cosyst.lexcl to 1 before they start - then the triggers won't fire, but also, all menu choices in the system are disabled. It so happens that my module is a batch operation, but it takes about an hour to do, so I was hoping to allow for concurrent use of the rest of the program while it was running, but frankly, the simplest solution, at least for the short term, would be just to use this built in mechanism, and tell my users that the module requires exclusive use of the system. The speed of the operation is greatly improved by turning off the trigger.


As far as the tests go - this is running in my test environment - on a single machine - Pentium 4, 1.7 GHz, 512 megs DDR ram, 80 gig IDE drive, workstation class machine, my client has a Pentium 3 - 800 Mhz, 512 megs of SDRAM, 40 gig SCSI drive, so my machine is faster as far as the processor goes, memory is roughly equivalent, and their SCSI drive is considerably faster I'm sure.

The bottom line on the trigger is that it executes really really slowly, in comparison to the update statements that are not in the trigger. I would like to do some further benchmarking to find out which components of the trigger slow it down. As I said in my other thread, I am under the impression that operations such as open cursor, fetch next run slowly, so I am going to start to benchmark those.


>David,
>
>I don't recommend turning off the trigger ... it would most likely raise hell with the accounting application. Not a good idea.
>
>Also, a question ... are you running your tests on the actual server? Or are you doing this on a copy of the database running on your own computer? Most servers have beefed up processors and lotsa RAM and can perform their operations much faster than your own computer would. So, before you make any judgements on the speed of the update, you'd need to actually run it on the server and not on your own computer. If you're already doing that, then ... oh well, I guess we'd need to come up with some other ideas ...
>
>~~Bonnie
>
>
>>What would be the best way to temporarily turn off the processing done by a sql server trigger, depending on the context, i.e. what code triggered it to fire. I this particular case I am working with a pre-existing database that already has the trigger code written. The trigger on the table 'sostrs' updates rows in 3 other tables. But, in the program module I added, the trigger code is not relevant to the update I am performing. (Although performing the code does not cause any problems in turns of the state of the database - the only problem is that the trigger is slowing down the execution of my code. In fact it turns out that the execution of the trigger is dramatically slowing down my update code. I discussed this in a separate thread (thread #681374).
>>
>>It would be easy enough to update a column in a table created specifically for this purpose with some flag that indicates that the update code in my routine is about to run, and then change the trigger so that it just returns without doing any processing, if the flag is set, and then unsets the flag when the updates are done. But in a multiuser situation, there is the chance that others might attempt an update at the time the flag was set, and then the code that is necesssary for their case would not run.
>>
>>Any suggestions on how to handle this would be appreciated.
>>