Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!hermes.visi.com!news-out.visi.com!petbe.visi.com!news1.optus.net.au!optus!snewsf0.syd.ops.aspac.uu.net!nnrp1.ozemail.com.au!53ab2750!not-for-mail
From: "Ray Teale" <ray@BLAHholly.com.au>
Newsgroups: comp.databases.oracle.server
Subject: Problem with INSTEAD OF triggers and BLOBS.
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Message-ID: <kQxCb.605$Tq.16346@nnrp1.ozemail.com.au>
Date: Sat, 13 Dec 2003 16:58:10 +1100
NNTP-Posting-Host: 203.166.24.90
X-Trace: nnrp1.ozemail.com.au 1071294864 203.166.24.90 (Sat, 13 Dec 2003 16:54:24 EST)
NNTP-Posting-Date: Sat, 13 Dec 2003 16:54:24 EST
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:250048

Oracle version 8.1.7

We are using a third party product which inserts, update and deletes records
in an Oracle table containing BLOBS.

I am trying to use an INSTEAD OF trigger to redirect the DML operations into
a different table and to perform some other auditing stuff.

To do this I have renamed to original table (TABX) to TABX_T (key,
blobcol) - and created a view TABX as select key, blobcol from TABX_T.  On
the view I have created an INSTEAD OF trigger which has code a bit that
shown below.

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?

Regards

Ray

_____________________________
CREATE OR REPLACE TRIGGER TAB_AUDIT
INSTEAD OF INSERT OR UPDATE OR DELETE ON TABX
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

IF INSERTING THEN
        BEGIN
        /*
         Insert into the underlying TABX_T table instead of the view
        */
        INSERT INTO TABX_T_T (key, blobcol)
        VALUES (:new.key, :new.blobcol);
        /*
        **      Audit the operation
        */
        INSERT INTO tabx_audit
        (key, blobcol, operation, audit_time, sequence)
        VALUES
        (:new.key,:new.blobcol
                ,'I',current_time, audit_seq.nextval);

ELSIF UPDATING THEN
        BEGIN
        /*
        ** Update TABX_T table
        */
        update TABX_T
        set blobcol = :new.blobcol
        where key = :new.key;

        INSERT INTO tabx_audit
        (key, blobcol, operation, audit_time, sequence)
        VALUES
        (:new.key,:new.blobcol
                ,'U',current_time, audit_seq.nextval);

etc.....