Re: Does a trigger eat resources when it doesn't do anything(No triggering event happens)
From: Palooka <nobody_at_nowhere.com>
Date: Wed, 15 Apr 2009 04:55:40 +0100
Message-ID: <0VcFl.15657$oi.13199_at_newsfe17.ams2>
Michael Austin wrote:
> Phper wrote:
>
>
> The "code" is evaluated only if the triggering event occurs.
>
> If the trigger is:
> CREATE or REPLACE TRIGGER trigger_name
> BEFORE UPDATE
> ON table_name
> [ FOR EACH ROW ]
> DECLARE
> -- variable declarations
> BEGIN
> -- trigger code
> EXCEPTION
> WHEN ...
> -- exception handling
> END;
>
> What gets executed if it is an INSERT statement is
> example pseudo-code.
> insert into table...
> does trigger exist -
> Y - evaluate -
> is it before/after insert - Y - execute stuff
> N - exit
> N - then continue
>
> In this case I am not sure there are any ramifications what-so-ever on
> memory if the trigger does nothing - it is insignificant as it is just
> few nano-seconds on the CPU. If the "execute stuff" does full table
> scans to update something else, yeah, that could be a problem.
>
> http://lmgtfy.com/?q=oracle+triggers+implementation
>
> Now, if you do the trigger incorrectly, it can have adverse effects. One
> example was where a table had a trigger that would ALWAYS update a field
> that would cause the trigger to fire and it would update 5 other tables
> that in turn would fire 6 additional triggers on those tables - for
> EVERY update. And they wondered why their updates were soooo
> slooowww.... I had them update their code to only update that one field
> at the appropriate time, and not every update.
>
> What problem are you trying to solve?
Michael,
Date: Wed, 15 Apr 2009 04:55:40 +0100
Message-ID: <0VcFl.15657$oi.13199_at_newsfe17.ams2>
Michael Austin wrote:
> Phper wrote:
>> How is a trigger implemented? Does a trigger eat resources when it >> doesn't do anything(No triggering event happens). IMAO, I think that >> because a trigger needs to keep alert, so it often consumes memory >> even if it doesn't do "anything"(No triggering event happens.) Am I >> right?
>
>
> The "code" is evaluated only if the triggering event occurs.
>
> If the trigger is:
> CREATE or REPLACE TRIGGER trigger_name
> BEFORE UPDATE
> ON table_name
> [ FOR EACH ROW ]
> DECLARE
> -- variable declarations
> BEGIN
> -- trigger code
> EXCEPTION
> WHEN ...
> -- exception handling
> END;
>
> What gets executed if it is an INSERT statement is
> example pseudo-code.
> insert into table...
> does trigger exist -
> Y - evaluate -
> is it before/after insert - Y - execute stuff
> N - exit
> N - then continue
>
> In this case I am not sure there are any ramifications what-so-ever on
> memory if the trigger does nothing - it is insignificant as it is just
> few nano-seconds on the CPU. If the "execute stuff" does full table
> scans to update something else, yeah, that could be a problem.
>
> http://lmgtfy.com/?q=oracle+triggers+implementation
>
> Now, if you do the trigger incorrectly, it can have adverse effects. One
> example was where a table had a trigger that would ALWAYS update a field
> that would cause the trigger to fire and it would update 5 other tables
> that in turn would fire 6 additional triggers on those tables - for
> EVERY update. And they wondered why their updates were soooo
> slooowww.... I had them update their code to only update that one field
> at the appropriate time, and not every update.
>
> What problem are you trying to solve?
Michael,
I concur with everything you have said. A couple of additional comments:
- A WHEN clause will stop the trigger from firing unnecessarily.
- I am with the Tom Kyte school of thought. Triggers are best avoided altogether anyway, in favour of decent package design. As you said, update what needs updating when it is needed. Automagic actions from triggers can be a nightmare to trace, and if unnecessary updates are done, a real drag om performance.
Palooka Received on Tue Apr 14 2009 - 22:55:40 CDT