Re: Long Raw / LOB in a Trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 25 Nov 1998 00:22:21 GMT
Message-ID: <365c4b73.2410486@192.86.155.100>

A copy of this was sent to Kirk Cameron <cameron_at_bicnet.net> (if that email address didn't require changing) On Tue, 24 Nov 1998 18:23:59 -0500, you wrote:

>I need to create a trigger to perform application specific auditing on a
>
>table containing a BLOB or a Long Raw column. I have tried both with
>little sucess.
>
>Base table
>
>t_item
>id number(10) not null PK
>blobcol blob
>
>I have an insert/update trigger on T_ITEM that tries to do the following
>
>insert into t_item_audit (id, blobcol, mod_dt) values(:new.id,
>:new.blobcol,sysdate);
>
>When the trigger fires I get the following error:
>
>ORA-22275: invalid LOB locator specified
>
>I tried a similar approach with a LONG RAW but :new/:old bind variables
>are not supported for LONG RAW columns.
>

What you are hitting is (from the application developers guide on LOBS):

 Triggers are not supported on LOBs. However, you can use a LOB in the body of a trigger as follows:

• in regular triggers old values may be read but not written and new values may not be read nor written.
• in 'instead of triggers', the:old and:new values can be read but not written.

  ---

So, you have a 'regular' trigger and are attempting to READ the :new value. What you can do is something like this (since you are reading :new values)

drop table demo;
drop table audit_tab;

create table demo ( x int primary key, y clob ); create table audit_tab ( x int, y clob );

create or replace package demo_pkg
as

    type pkArray is table of demo.x%type index by binary_integer;

    empty pkArray;
    newones pkArray;
end;
/

create or replace trigger demo_biu
before insert or update on demo
begin

    demo_pkg.newones := demo_pkg.empty; end;
/
create or replace trigger demo_biufer
before insert or update on demo for each row begin

    demo_pkg.newones(demo_pkg.newones.count+1) := :new.x; end;
/
create or replace trigger demo_aiu
after insert or update on demo
begin

    for i in 1 .. demo_pkg.newones.count loop

        insert into audit_tab select * from demo where x = demo_pkg.newones(i);     end loop;
end;
/

that will save all of the INSERTED and UPDATED primary keys and then insert them into the audit table in an AFTER trigger -- the AFTER (not for each row) trigger can read these values safely...
h
Hope this helps.

>Any help would be appreciated.
>
>Kirk
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Nov 24 1998 - 18:22:21 CST