Re: LOB with trigger

In article <f1fc67fb.0412261024.1b474b13_at_posting.google.com>, mm says...
>
>My question is about lobs within trigger.
>I have to create triggers on insert/update/delete on tables in order
>to perform specific auditing.
>For regular column type,it is ok.
>But for blob column type, i do not have a good lob locator in the :new
>variable.
>
>ex:
>create table bb (id number,name varchar2(10),obj blob);
>create table bbaudit(id number,action char(1),timest date,name
>varchar2(10),obj blob);
>
>create trigger TG_BB_UI
>BEFORE INSERT OR UPDATE on BB
>FOR EACH ROW
>DECLARE
>sAction CHAR(1);
>BEGIN
>IF INSERTING THEN
> sAction:='I';
>ELSIF UPDATING THEN
> sAction:='U';
>END IF;
>insert into bbaudit (ID,ACTION,TIMEST,NAME,OBJ)
>values (:new.id,sAction,sysdate,:new.name,:new.OBJ);
>END;
>
>According to Oracle, trigger does not support LOBs.
>In regular trigger :old reference can be read but not written AND :new
>reference cannot be read nor written.
>
>Someone does have an idea about how to resolve this restriction.

this is an unsolvable problem with triggers - most LOBS are populated this way:

insert into t .... values ( ...., EMPTY_CLOB() ) returning that_clob into somevariable;

dbms_lob.writeAppend
dbms_lob.writeAppend

and so on. and even if they are not, to update a lob you would:

select lob from t where ... for update;

dbms_lob.write....

(dbms_lob.write... is replaced by whatever API you might be using - jdbc, whatever have "methods" to operate on lobs)

In general, lob modifications *fire no triggers whatsoever*. take the insert case, the INSERT fired the trigger. The modification of the lob -- it happened after the fact. Look at the "update" of the lob -- no triggers are fired at all.

You should consider wrapping your application with transactional API's in the database to capture what you want to capture (eg: triggers won't cut it for you here -- don't have the apps do sql, have them call API's that do sql and perform whatever auditing you like)

or you could consider Streams in 9ir2 and up - mine the redo information after the fact to capture changes.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation