Triggers and LOBs: Synchronous CDC and Synchronous Streams Capture

Just a quick post to outline some of the limits of using LOBs and triggers together; there is no secret, it's written (black on white) in the manual : depending how a LOB column is updated, triggers on the table will fire... or NOT ! The reason is indeed related to the design of the LOB Locator that allow data chunk updates without any reference to the changed row.

Anyway, I don't want to dig too much into the details of why things are like they are. Instead, you'll find below some PL/SQL code samples. Those pieces of code show that, if you work with the LOB locator, the triggers won't fire. On the other side, if you update the LOB columns directly or create a locator, in that case, the triggers will fire.

Create a table and a "for each row" trigger

To begin, we'll create a table with a trigger. That trigger increments the number of times it fires in a separate table named xlog :

create table x (id number, text clob)
      lob(text) store as securefiles;

create table xlog (id number, call number);

create or replace trigger x_trigger
    after insert or update or delete on x
    for each row
begin
   if inserting then
      insert into xlog (id, call) values (:new.id, 1);
   else
      update xlog set call=call+1 where id=:old.id;
   end if;
end;
/

Note:
To test Oracle Database 11g Release 2 Streams new features (in a latter post), I've used a securefile LOB.

Changes to LOB does fire triggers

To begin, we change a few row data without using any LOB persistent locator. As you'll discover in the xlog table every row changes fire the trigger:

insert into x values (1,null);
update x set text='X' where id=1;
update x set text='XY' where id=1;
commit;

select x.text, xlog.call
   from x, xlog
  where x.id=1
    and xlog.id=1;

TEXT CALL
---- ----
   XY    3

Changes to LOB doesn't fire triggers

On the other side, if you change the row data with the persistent LOB Locator like below, you'll find that the trigger doesn't fire:

declare
  my_x clob;
begin
  select text into my_x
    from x where id=1
     for update;
   dbms_lob.append(my_x,'Z');
   dbms_lob.append(my_x,'Z');
   commit;
end;
/

select x.text, xlog.call
   from x, xlog
  where x.id=1
    and xlog.id=1;

TEXT CALL
---- ----
XYZZ    3

Conclusion & Cleanup

If you just think about it, this may feed your thoughts about the LOB support (or actually non support!) with Synchronous Change Data Capture (CDC) and Synchronous Streams Capture. But enough of conclusions for today, lets just drop the tables and its trigger:

drop table x purge;
drop table xlog purge;