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 [EMAIL PROTECTED] 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 ---------------------------------------------------------------------------- ---- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -----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 [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
