John,
I can't see how the trigger itself could be causing performance issues. I assume you've streamlined the trigger itself. If it passes initial decisions about whether there is something to be audited, what does it do then - update another table, or insert? We have triggers on three inventory tables that are all large. Any auditable activity writes 1-2 records in one of three external databases via an Oterro DSN. No performance hits. Try this: At the top of the trigger code, before you do anything else: SET VAR texecute INTEGER IF texecute IS NOT NULL AND texecute = 0 THEN RETURN 0 ENDIF . rest of trigger code Now, whenever you want to avoid the trigger executing: SET VAR texecute INTEGER = 0 . do whatever CLEAR VAR texecute The trigger will fire, but do nothing (very quickly.) Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of John Engwer Sent: Tuesday, August 04, 2009 9:20 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Triggers and performance A while ago I was fond of using triggers to trap and collect data until I drove my application to its knees with triggers. I found that using a trigger on a heavily accessed table can have a serious impact on performance. I have tried using bypass code to shunt the trigger but it still causes performance problems. Just the fact that the trigger is fired on every table update creates tremendous overhead in an active system. Is there a way to turn a trigger on and off for a specific user and process? I do not want to drop the trigger because I want the audit data for processes that other users are performing. Here is my scenario: A master table of all products (typically 1 - 2 million records). This table is heavily accessed. I want to create an audit file to track changes to the table (update trigger works well except for the performance issue) I would like to bypass the trigger during certain processes such as the update that occurs after a physical inventory. Any recommendations will be appreciated. Razzak - Schedule permitting, could we spend a little time discussing triggers in the advanced training next week? John
