| 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 fires when "WHEN" clause evals to false?
Sorry, I should have mentioned that the app doesn't allow NULLs, even though
there's no constraint on that column. The column is type CHAR. The
:old.commodity_code check in the WHEN clause is there to account for
INSERTed rows. The table has about 200K rows total (inventory part master),
with only 1-to-10 rows inserted per day normally. Many 1000s of UPDATEs per
day, potentially, yes.
Thanks,
Rich
Rich Jesse System/Database Administrator rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
-----Original Message-----
Sent: Wednesday, July 30, 2003 2:20 PM
To: Multiple recipients of list ORACLE-L
the trigger will fire if :new.commodity_code is null as well ... Raj
-----Original Message-----
Sent: Wednesday, July 30, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L
Since I'm getting nowhere fast with Oracle Support, I'll try this here: While looking in V$DB_OBJECT_CACHE, we noticed an unpinned row-level trigger
had been fired over 900K times. We compared this to other row-level triggers
on the same table and noticed that the other triggers were only executed
around
1000 or 2000 times. From knowledge of the business, it would seem that the
latter is much more believable. The major difference between this trigger
and
the others is that the others all have a column declared in the FOR UPDATE
clause of the trigger. For example, here's the header of the trigger in
question:
CREATE OR REPLACE TRIGGER QT_BLAH_BLAH_IU
AFTER INSERT OR UPDATE ON my_table
FOR EACH ROW
WHEN (NEW.commodity_code != OLD.commodity_code OR OLD.commodity_code IS
NULL)
...while the other triggers would also contain an "OF my_column" clause
immediately following "AFTER INSERT OR UPDATE".
The table contains about 200K rows. Having this trigger fire 900K times is
highly unlikely, given the WHEN clause. According to Oracle8i Application
Developer's Guide - Fundamentals, about the WHEN clause of a row-level
trigger:
"If the expression evaluates to TRUE for a row, then the trigger body is
fired
on behalf of that row. However, if the expression evaluates to FALSE or NOT
TRUE for a row (unknown, as with nulls), then the trigger body is not fired
for
that row."
The same manual also says:
"If a triggering statement specifies UPDATE, then an optional list of
columns
can be included in the triggering statement. If you include a column list,
then
the trigger is fired on an UPDATE statement only when one of the specified
columns is updated. If you omit a column list, then the trigger is fired
when
any column of the associated table is updated."
This leaves me a little confused as to what exactly fires when with a
trigger.
I know that bug 1764313 says the execution count is inaccurate, but if
anything
that bug would suggest that the 900K rows is LOW and not HIGH like we've
seen.
I guess my real question is what part of a trigger is loaded when. Is there
a
difference between the trigger and the trigger body when it comes to
executions? How is this difference shown the V$DB_OBJECT_CACHE or V$SQLAREA
for that matter? Is the distinction similar to PACKAGE vs. PACKAGE BODY?
BTW, if it matters, we're currently on 32-bit 8.1.7.4.0 on 64-bit HP/UX
11.0.
TIA,
Rich
Rich Jesse System/Database Administrator rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: Rich.Jesse_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-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jul 30 2003 - 15:04:23 CDT
 |
 |