Trigger Firing and Execution of Body Code

Rich noticed at a large number of executions for a trigger on a table the header looks like this and now we are a bit confused. Consider the following:

AFTER INSERT OR UPDATE ON BLAH.PARTMASTER  FOR EACH ROW
 WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS NULL)
BEGIN
(Trigger with many executions)

ON another trigger with a OF clause

 AFTER INSERT OR UPDATE OF commodity_code ON blah.partmaster FOR EACH ROW
WHEN (new.commodity_code != old.commodity_code) BEGIN
(Trigger with few executions)

Is the following statement true?

A trigger can fire but will not always execute the code in the body.

I believe it is based on the examples above. The top trigger will fire on every insert and update of any column in the table but will not run code in the body and just exit thus incrementing an execution stat. The second trigger will execute when the commodity code column is updated. Keep in mind I am saying here EXECUTE not FIRE the BODY.

So a trigger is said to execute when the condition for the trigger (AFTER INSERT OR UPDATE in this case) is met.

The interesting issue to is that the first trigger grabs 8k of memory every time it fires and it has executed 900,000 time in the past month and a half. I suspect that a trigger is a continuous package of compiled code that when the initial condition of ON INSERT OR UPDATE ON TABLE executes the trigger code. The WHEN clause in the code is simple logical branching condition like an IF statement. When the condition is not met it skips the body and continues to the end resulting in the trigger "executing" or "firing" even though the body was not processed.

questions, comments, suggestions?
thoughts, feelings, ideas?
statements, opinions, conjecture? Received on Thu Jul 24 2003 - 09:12:53 CDT