Problem with INSTEAD OF triggers and BLOBS.

From: Ray Teale <ray_at_BLAHholly.com.au>
Date: Sat, 13 Dec 2003 16:58:10 +1100
Message-ID: <kQxCb.605$Tq.16346@nnrp1.ozemail.com.au>

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..... Received on Fri Dec 12 2003 - 23:58:10 CST