Re: :old.clob and :new.clob in triggers
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Thu, 17 Jun 2010 07:37:00 -0700 (PDT)
Message-ID: <59b94944-eeed-4d6b-bd2a-dee3060d61a2_at_s9g2000yqd.googlegroups.com>
On Jun 17, 2:04 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Jun 17, 12:58 pm, Deb <debashish.majum..._at_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
Date: Thu, 17 Jun 2010 07:37:00 -0700 (PDT)
Message-ID: <59b94944-eeed-4d6b-bd2a-dee3060d61a2_at_s9g2000yqd.googlegroups.com>
On Jun 17, 2:04 pm, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Jun 17, 12:58 pm, Deb <debashish.majum..._at_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. Received on Thu Jun 17 2010 - 09:37:00 CDT