:old.clob and :new.clob in triggers
in [Oracle]
|
Prev: rman issue
Next: Data Pump and GRANTs
From: Deb on 17 Jun 2010 04:58 Hi, I've a table with a clob column. In a trigger that I need to write, I need to compare the old and new values for this column. The code inside the trigger body looks like this: CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR DELETE ON tbl_clob REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW IF UPDATING THEN IF :NEW.clob_col <> :OLD.clob_col THEN INSERT INTO ... END IF; END IF; ...... END; / Is this the right way of comparing the old and new values for a clob, inside the trigger? Please suggest. -- Thanks DeB
From: John Hurley on 17 Jun 2010 07:21 Deb: > I've a table with a clob column. In a trigger that I need to write, I > need to compare the old and new values for this column. > > The code inside the trigger body looks like this: > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > DELETE > ON tbl_clob > REFERENCING NEW AS NEW OLD AS OLD > FOR EACH ROW > > IF UPDATING THEN > IF :NEW.clob_col <> :OLD.clob_col > THEN > INSERT INTO ... > END IF; > END IF; > ..... > > END; > / > > Is this the right way of comparing the old and new values for a clob, > inside the trigger? Please suggest. We don't have any CLOBs but my guess is that you would use dbms_lob package. Isn't there a compare function or similar?
From: Vladimir M. Zakharychev on 17 Jun 2010 08:04 On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > Hi, > > I've a table with a clob column. In a trigger that I need to write, I > need to compare the old and new values for this column. > > The code inside the trigger body looks like this: > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > DELETE > ON tbl_clob > REFERENCING NEW AS NEW OLD AS OLD > FOR EACH ROW > > IF UPDATING THEN > IF :NEW.clob_col <> :OLD.clob_col > THEN > INSERT INTO ... > END IF; > END IF; > ..... > > END; > / > > Is this the right way of comparing the old and new values for a clob, > inside the trigger? Please suggest. > > -- > Thanks > DeB Comments embedded in the code below. .... -- this is to limit the scope of the following IF - we don't want -- to waste CPU cycles and I/O bandwidth comparing contents of old -- and new CLOB values when it definitely was not touched by the -- update and thus didn't change. IF UPDATING('CLOB_COL') THEN -- this is the right way of comparing LOBs. When you compare like -- clob1 <> clob2, Oracle implicitly converts your CLOBs to -- VARCHAR2s (cutting off everything past the first 32k bytes) and -- compares strings. If the difference is somewhere past the first -- 32k bytes, this comparison will yield equality which is -- obviously not correct. DBMS_LOB.COMPARE() compares LOBs fully -- and returns 0 if the are equal. It does not throw exceptions -- for BLOB and CLOB comparisons, so it's pretty safe to use in -- triggers. Note that it can return NULL for some non-equal LOBs, -- so we need to use a variable and test if it's 0 or NULL - both -- cases indicate the LOBs are different. DECLARE RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); BEGIN IF RES IS NULL OR RES != 0 THEN -- do your stuff, they are different ... END; .... Hth, Vladimir M. Zakharychev N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
From: Carlos on 17 Jun 2010 10:37 On Jun 17, 2:04 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > > > > > Hi, > > > I've a table with a clob column. In a trigger that I need to write, I > > need to compare the old and new values for this column. > > > The code inside the trigger body looks like this: > > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > > DELETE > > ON tbl_clob > > REFERENCING NEW AS NEW OLD AS OLD > > FOR EACH ROW > > > IF UPDATING THEN > > IF :NEW.clob_col <> :OLD.clob_col > > THEN > > INSERT INTO ... > > END IF; > > END IF; > > ..... > > > END; > > / > > > Is this the right way of comparing the old and new values for a clob, > > inside the trigger? Please suggest. > > > -- > > Thanks > > DeB > > Comments embedded in the code below. > > ... > -- this is to limit the scope of the following IF - we don't want > -- to waste CPU cycles and I/O bandwidth comparing contents of old > -- and new CLOB values when it definitely was not touched by the > -- update and thus didn't change. > IF UPDATING('CLOB_COL') THEN > -- this is the right way of comparing LOBs. When you compare like > -- clob1 <> clob2, Oracle implicitly converts your CLOBs to > -- VARCHAR2s (cutting off everything past the first 32k bytes) and > -- compares strings. If the difference is somewhere past the first > -- 32k bytes, this comparison will yield equality which is > -- obviously not correct. DBMS_LOB.COMPARE() compares LOBs fully > -- and returns 0 if the are equal. It does not throw exceptions > -- for BLOB and CLOB comparisons, so it's pretty safe to use in > -- triggers. Note that it can return NULL for some non-equal LOBs, > -- so we need to use a variable and test if it's 0 or NULL - both > -- cases indicate the LOBs are different. > DECLARE > RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); > BEGIN > IF RES IS NULL OR RES != 0 THEN > -- do your stuff, they are different > ... > END; > ... > > Hth, > Vladimir M. Zakharychev > N-Networks, makers of Dynamic PSP(tm) > http://www.dynamicpsp.com Vladimir: I seem to recall that "IF UPDATING('CLOB_COL') THEN" will fire if the column is in the updating list (set clob_col = ...) no matter if the new and the old values are equal or not. Cheers. Carlos.
From: ddf on 17 Jun 2010 11:00
On Jun 17, 10:37 am, Carlos <miotromailcar...(a)netscape.net> wrote: > On Jun 17, 2:04 pm, "Vladimir M. Zakharychev" > > > > > > <vladimir.zakharyc...(a)gmail.com> wrote: > > On Jun 17, 12:58 pm, Deb <debashish.majum...(a)gmail.com> wrote: > > > > Hi, > > > > I've a table with a clob column. In a trigger that I need to write, I > > > need to compare the old and new values for this column. > > > > The code inside the trigger body looks like this: > > > > CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR > > > DELETE > > > ON tbl_clob > > > REFERENCING NEW AS NEW OLD AS OLD > > > FOR EACH ROW > > > > IF UPDATING THEN > > > IF :NEW.clob_col <> :OLD.clob_col > > > THEN > > > INSERT INTO ... > > > END IF; > > > END IF; > > > ..... > > > > END; > > > / > > > > Is this the right way of comparing the old and new values for a clob, > > > inside the trigger? Please suggest. > > > > -- > > > Thanks > > > DeB > > > Comments embedded in the code below. > > > ... > > -- this is to limit the scope of the following IF - we don't want > > -- to waste CPU cycles and I/O bandwidth comparing contents of old > > -- and new CLOB values when it definitely was not touched by the > > -- update and thus didn't change. > > IF UPDATING('CLOB_COL') THEN > > -- this is the right way of comparing LOBs. When you compare like > > -- clob1 <> clob2, Oracle implicitly converts your CLOBs to > > -- VARCHAR2s (cutting off everything past the first 32k bytes) and > > -- compares strings. If the difference is somewhere past the first > > -- 32k bytes, this comparison will yield equality which is > > -- obviously not correct. DBMS_LOB.COMPARE() compares LOBs fully > > -- and returns 0 if the are equal. It does not throw exceptions > > -- for BLOB and CLOB comparisons, so it's pretty safe to use in > > -- triggers. Note that it can return NULL for some non-equal LOBs, > > -- so we need to use a variable and test if it's 0 or NULL - both > > -- cases indicate the LOBs are different. > > DECLARE > > RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL); > > BEGIN > > IF RES IS NULL OR RES != 0 THEN > > -- do your stuff, they are different > > ... > > END; > > ... > > > Hth, > > Vladimir M. Zakharychev > > N-Networks, makers of Dynamic PSP(tm) > > http://www.dynamicpsp.com > > Vladimir: > > I seem to recall that "IF UPDATING('CLOB_COL') THEN" will fire if the > column is in the updating list (set clob_col = ...) no matter if the > new and the old values are equal or not. > > Cheers. > > Carlos.- Hide quoted text - > > - Show quoted text - That is the desired behaviour. Notice the subsequent code that handles whether the clob values differ. Vladmir's code won't fire if the clob column is NOT in the update list; it doesn't matter if the clob is being updated with the same values as the IF RES ... section addresses that possibility. David Fitzjarrell |