Re: Problem with INSTEAD OF triggers and BLOBS.

Yes I think your right. Thanks for your input. Oracle doc does say that trigger values :new and :old are not supported for BLOBS Regards

Ray

"Jonathan Gennick" <jonathan_at_gennick.com> wrote in message news:4j2mtv8bghsqg8c2ao90s79c3v0dump4p8_at_4ax.com...
> On Sat, 13 Dec 2003 16:58:10 +1100, "Ray Teale" <ray_at_BLAHholly.com.au>
> wrote:
>
> >The problem I have is that the BLOB field does not update correctly.
> >Specifically it seems the third party application is doing some BLOB
> >manipulation in the update which is not caught by my UPDATE redirect.
Can
> >anybody shed some light on this for me?
>
> BLOBs can be manipulated via their locator. It's been awhile since
> I've worked with LOBs, and I'm almost afraid to say this without
> double-checking by writing some code, but I believe you can select the
> locator, read and write from the BLOB, and your changes will be
> recorded without the need for you to issue an UPDATE statement. An
> UPDATE trigger then, would not catch the update. That's an interesting
> scenario, actually, and one that I've not thought of until now.
>
> From a certain point of view, the value of a BLOB column is really the
> locator, which points to the location of the BLOB in the datafile. If
> you update a locator, your trigger would catch it. If you just write
> through the locator to change the BLOB, your trigger would not fire.
>
> Bear in mind that I'm conjecturing a bit here. Based on what I know,
> the above all makes sense to me as I write this over my morning
> coffee, but that doesn't necessarily mean I'm right.
>
> This could be a good article idea. If a trigger won't capture changes
> to LOBs, then what will? Or *can* you even capture such changes?
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com
>
> Join the Oracle-article list and receive one article on Oracle
> technologies per month by email. To join, visit
> http://four.pairlist.net/mailman/listinfo/oracle-article, or send
> email to Oracle-article-request_at_gennick.com and include the word
> "subscribe" in either the subject or body.
Received on Sat Dec 13 2003 - 17:17:00 CST