Re: :old.clob and :new.clob in triggers
Date: Fri, 18 Jun 2010 04:21:51 -0700 (PDT)
Message-ID: <194f6e15-139a-4d27-9411-95c378822290_at_g19g2000yqc.googlegroups.com>
On Jun 17, 8:06 pm, Timur Akhmadeev <timur.akhmad..._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
>
> Hi,
>
> you can not write a trigger which will handle all kinds of updates to
> LOBs. For details refer to the documentation:http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/create....
>
> Timur Akhmadeev
That's correct, though a bit out of scope of OP's question and thus I omitted this issue. Things get tricky when you alter the LOB contents via OCI or DBMS_LOB. Additional code should be written to get around this "OCI and DBMS_LOB changes to LOBs do not fire triggers" behavior. Possible solution could be to always create a temporary LOB, copy the source LOB into it, alter the temporary LOB and then do an update with SET clob_col=temp_lob, which will fire triggers. Depending on the source LOB size, this copy to temp approach may add significant overhead, but there seem to be no other way to catch any and all LOB changes in triggers.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Fri Jun 18 2010 - 06:21:51 CDT