Re: :old.clob and :new.clob in triggers
From: ddf <oratune_at_msn.com>
Date: Thu, 17 Jun 2010 08:00:16 -0700 (PDT)
Message-ID: <6e4ce78c-f649-4cda-86f3-7ff02c55efdc_at_s6g2000prg.googlegroups.com>
On Jun 17, 10:37 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 17 Jun 2010 08:00:16 -0700 (PDT)
Message-ID: <6e4ce78c-f649-4cda-86f3-7ff02c55efdc_at_s6g2000prg.googlegroups.com>
On Jun 17, 10:37 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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.- 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 Received on Thu Jun 17 2010 - 10:00:16 CDT