Set Triggers Disabled and 4GL
All , I have a situation where I need to update a date in a table . However the table has an update trigger which calls three stored procedures . As the no of updates is to the order of 200000 daily , the trigger is causing the updates to be extermely slow . I am planning to "set triggers for table disabled" using 4GL by preparing and executing it and enabling them when the program ends.. Will this statement cause the triggers to be disabled for the session or the user or will it disable the trigger for all sessions and users (this would not be a desirable situation). Is there any other strategy to avoid the performance hit because of the trigger firing . NSV sending to informix-list
Nsvirk wrote: > I have a situation where I need to update a date in a table. However the > table has an update trigger which calls three stored procedures. > As the no of updates is to the order of 200000 daily, the trigger is > causing the updates to be extremely slow. So, why are you doing three stored procedures? Why not just one? Are you sure there isn't a better way to achieve the result? > I am planning to "set triggers for table disabled" using 4GL by preparing > and executing it and enabling them when the program ends.. > > Will this statement cause the triggers to be disabled for the session or the > user or will it disable the trigger for all sessions and users (this would > not be a desirable situation). Distinguish between deferring and disabling constraints in general. However, for triggers, you can only enable and disable them - that applies across sessions. > Is there any other strategy to avoid the performance hit because of the > trigger firing . What would the triggered procedures do? Is it safe to turn off the triggers - so the triggered procedures are not invoked? If so, go ahead. But remember, it would apply to all updates - not just those your I4GL program is executing. If they're that expendable, are you sure they're necessary in the first place? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/