Re: Problem with INSTEAD OF triggers and BLOBS.

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 - 06:48:14 CST