| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Trigger Firing and Execution of Body Code
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------=_NextPartTM-000-c1447889-f6ec-4d4a-bf6a-020516609e5a Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C351EF.BF40686E"
------_=_NextPart_001_01C351EF.BF40686E
Content-Type: text/plain;
charset="iso-8859-1"
brad,
you have got it right .... but to prove your assumptions .. it is very easy ...
alter session set events '10046 trace name context forever, level 4'
/
run some dml on the table
alter session set events '10046 trace name context off'
/
Then look through trace file, it will show you the logic used to fire or skip the trigger execution.
Raj
-----Original Message-----
From: Odland, Brad [mailto:Brad.Odland_at_qtiworld.com]
Sent: Thursday, July 24, 2003 11:10 AM
To: Multiple recipients of list ORACLE-L
Subject: 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?
Am I nuts, should I care?
Brad O.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Odland, Brad INET: Brad.Odland_at_qtiworld.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Jul 24 2003 - 09:27:44 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C351EF.BF40686E Content-Type: text/html; charset="iso-8859-1" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1"> <META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.19"> <TITLE>RE: Trigger Firing and Execution of Body Code</TITLE> </HEAD> <BODY> <P><FONT SIZE=2>brad,</FONT> </P> <P><FONT SIZE=2>you have got it right .... but to prove your assumptions .. it is very easy ...</FONT> </P> <P><FONT SIZE=2>alter session set events '10046 trace name context forever, level 4'</FONT> <BR><FONT SIZE=2>/</FONT> <BR><FONT SIZE=2>run some dml on the table</FONT> <BR><FONT SIZE=2>alter session set events '10046 trace name context off'</FONT> <BR><FONT SIZE=2>/</FONT> </P> <P><FONT SIZE=2>Then look through trace file, it will show you the logic used to fire or skip the trigger execution.</FONT> </P> <P><FONT SIZE=2>Raj</FONT> <BR><FONT SIZE=2>--------------------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>Rajendra dot Jamadagni at nospamespn dot com</FONT> <BR><FONT SIZE=2>All Views expressed in this email are strictly personal.</FONT> <BR><FONT SIZE=2>QOTD: Any clod can have facts, having an opinion is an art !</FONT> </P> <BR> <P><FONT SIZE=2>-----Original Message-----</FONT> <BR><FONT SIZE=2>From: Odland, Brad [<A HREF="mailto:Brad.Odland_at_qtiworld.com">mailto:Brad.Odland_at_qtiworld.com</A>]</FONT> <BR><FONT SIZE=2>Sent: Thursday, July 24, 2003 11:10 AM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=2>Subject: Trigger Firing and Execution of Body Code</FONT> </P> <BR> <P><FONT SIZE=2>Rich noticed at a large number of executions for a trigger on a table the</FONT> <BR><FONT SIZE=2>header looks like this and now we are a bit confused. Consider the</FONT> <BR><FONT SIZE=2>following:</FONT> </P> <BR> <P><FONT SIZE=2>AFTER INSERT OR UPDATE ON BLAH.PARTMASTER</FONT> <BR><FONT SIZE=2> FOR EACH ROW</FONT> <BR><FONT SIZE=2> WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS</FONT> <BR><FONT SIZE=2>NULL)</FONT> <BR><FONT SIZE=2>BEGIN</FONT> </P> <P><FONT SIZE=2>(Trigger with many executions)</FONT> </P> <BR> <P><FONT SIZE=2>ON another trigger with a OF clause</FONT> </P> <P><FONT SIZE=2> AFTER INSERT OR UPDATE OF commodity_code ON blah.partmaster</FONT> <BR><FONT SIZE=2>FOR EACH ROW</FONT> <BR><FONT SIZE=2>WHEN (new.commodity_code != old.commodity_code)</FONT> <BR><FONT SIZE=2>BEGIN</FONT> </P> <P><FONT SIZE=2>(Trigger with few executions)</FONT> </P> <BR> <P><FONT SIZE=2>Is the following statement true?</FONT> </P> <P><FONT SIZE=2>A trigger can fire but will not always execute the code in the body.</FONT> </P> <P><FONT SIZE=2>I believe it is based on the examples above. The top trigger will fire on</FONT> <BR><FONT SIZE=2>every insert and update of any column in the table but will not run code in</FONT> <BR><FONT SIZE=2>the body and just exit thus incrementing an execution stat. The second</FONT> <BR><FONT SIZE=2>trigger will execute when the commodity code column is updated. Keep in mind</FONT> <BR><FONT SIZE=2>I am saying here EXECUTE not FIRE the BODY.</FONT> </P> <P><FONT SIZE=2>So a trigger is said to execute when the condition for the trigger (AFTER</FONT> <BR><FONT SIZE=2>INSERT OR UPDATE in this case) is met.</FONT> </P> <P><FONT SIZE=2>The interesting issue to is that the first trigger grabs 8k of memory every</FONT> <BR><FONT SIZE=2>time it fires and it has executed 900,000 time in the past month and a half.</FONT> <BR><FONT SIZE=2>I suspect that a trigger is a continuous package of compiled code that when</FONT> <BR><FONT SIZE=2>the initial condition of ON INSERT OR UPDATE ON TABLE executes the trigger</FONT> <BR><FONT SIZE=2>code. The WHEN clause in the code is simple logical branching condition like</FONT> <BR><FONT SIZE=2>an IF statement. When the condition is not met it skips the body and</FONT> <BR><FONT SIZE=2>continues to the end resulting in the trigger "executing" or "firing" even</FONT> <BR><FONT SIZE=2>though the body was not processed.</FONT> </P> <P><FONT SIZE=2>questions, comments, suggestions?</FONT> <BR><FONT SIZE=2>thoughts, feelings, ideas?</FONT> <BR><FONT SIZE=2>statements, opinions, conjecture?</FONT> <BR><FONT SIZE=2>Am I nuts, should I care?</FONT> </P> <BR> <P><FONT SIZE=2>Brad O.</FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Please see the official ORACLE-L FAQ: http://www.orafaq.net</FONT> <BR><FONT SIZE=2>-- </FONT> <BR><FONT SIZE=2>Author: Odland, Brad</FONT> <BR><FONT SIZE=2> INET: Brad.Odland_at_qtiworld.com</FONT> </P> <P><FONT SIZE=2>Fat City Network Services -- 858-538-5051 http://www.fatcity.com</FONT> <BR><FONT SIZE=2>San Diego, California -- Mailing list and web hosting services</FONT> <BR><FONT SIZE=2>---------------------------------------------------------------------</FONT> <BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT SIZE=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT SIZE=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT SIZE=2>(or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT SIZE=2>also send the HELP command for other information (like subscribing).</FONT> </P> </BODY> </HTML> ------_=_NextPart_001_01C351EF.BF40686E-- ------=_NextPartTM-000-c1447889-f6ec-4d4a-bf6a-020516609e5a Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" *********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
 |
 |