Re: Does a trigger eat resources when it doesn't do anything(No triggering event happens)

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 14 Apr 2009 22:15:44 -0500
Message-ID: <MhcFl.4901$im1.3046_at_nlpi061.nbdc.sbc.com>



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? Received on Tue Apr 14 2009 - 22:15:44 CDT

Original text of this message