changing lobs with select for update and dbms_lob.write - no triggers are fired?

I have a table containing a LOB. The LOB is being changed by doing a "select 
for update" and a "dbms_lob.write" in PL/SQL. I notice that the value of the 
LOB is changed but no trigger is fired. Any ideas on how to track the LOB 
change through a trigger or some other mechanism? I tried this in Oracle 8.0, 
8.1, 9.2, 10.1: same behaviour in every version.
 
I think it's counter-intuitive that you could change a database column after a 
"select for update" but without an update command.
 
See below for script and sample run.
 
sample run:
 
SQL> drop table t ;
Table supprimée.
SQL> create table t (n number, c clob) ;
Table créée.
SQL> insert into t (n, c) values (1, 'ABC') ;
1 ligne créée.
SQL> commit ;
Validation effectuée.
 
SQL> create trigger t_b4d
  2   before delete on t
  3  begin
  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' delete on T') ;
  6     dbms_output.put_line ('*************') ;
  7  end ;
  8  /
Déclencheur créé.
SQL> create trigger t_b4i
  2   before insert on t
  3  begin
  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' insert on T') ;
  6     dbms_output.put_line ('*************') ;
  7  end ;
  8  /
Déclencheur créé.
SQL> create trigger t_b4u
  2   before update on t
  3  begin
  4     dbms_output.put_line ('*************') ;
  5     dbms_output.put_line (' update on T') ;
  6     dbms_output.put_line ('*************') ;
  7  end ;
  8  /
Déclencheur créé.
SQL> create trigger t_b4dr
  2   before delete on t
  3   for each row
  4  begin
  5     dbms_output.put_line ('+++++++++++++') ;
  6     dbms_output.put_line (' for each row trigger') ;
  7     dbms_output.put_line (' delete on T') ;
  8     dbms_output.put_line ('+++++++++++++') ;
  9  end ;
 10  /
Déclencheur créé.
SQL> create trigger t_b4ir
  2   before insert on t
  3  begin
  4     dbms_output.put_line ('+++++++++++++') ;
  5     dbms_output.put_line (' for each row trigger') ;
  6     dbms_output.put_line (' insert on T') ;
  7     dbms_output.put_line ('+++++++++++++') ;
  8  end ;
  9  /
Déclencheur créé.
SQL> create trigger t_b4ur
  2   before update on t
  3  begin
  4     dbms_output.put_line ('+++++++++++++') ;
  5     dbms_output.put_line (' for each row trigger') ;
  6     dbms_output.put_line (' update on T') ;
  7     dbms_output.put_line ('+++++++++++++') ;
  8  end ;
  9  /
Déclencheur créé.
 
SQL> set serveroutput on size 2000
SQL> select * from t ;
        N C
--------- 
--------------------------------------------------------------------------------
        1 ABC
 
SQL> update t set c = 'ABD' where n = 1 ;
+++++++++++++
for each row trigger
update on T
+++++++++++++
*************
update on T
*************
1 ligne mise à jour.
SQL> commit ;
Validation effectuée.
 
SQL> select * from t ;
        N C
--------- 
--------------------------------------------------------------------------------
        1 ABD
 
SQL> declare
  2     temp_lob clob ;
  3  begin
  4     select c into temp_lob
  5      from t
  6      where n = 1
  7      for update ;
  8     dbms_lob.write (lob_loc => temp_lob,
  9                     amount => 1,
 10                     offset => 3,
 11                     buffer => 'E') ;
 12     commit ;
 13  end ;
 14  /
Procédure PL/SQL terminée avec succès.
 
SQL> select * from t ;
        N C
--------- 
--------------------------------------------------------------------------------
        1 ABE
SQL> 
 
 
script:
 
drop table t ;
create table t (n number, c clob) ;
insert into t (n, c) values (1, 'ABC') ;
commit ;
create trigger t_b4d
 before delete on t
begin
   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' delete on T') ;
   dbms_output.put_line ('*************') ;
end ;
/
create trigger t_b4i
 before insert on t
begin
   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' insert on T') ;
   dbms_output.put_line ('*************') ;
end ;
/
create trigger t_b4u
 before update on t
begin
   dbms_output.put_line ('*************') ;
   dbms_output.put_line (' update on T') ;
   dbms_output.put_line ('*************') ;
end ;
/
create trigger t_b4dr
 before delete on t
 for each row
begin
   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' delete on T') ;
   dbms_output.put_line ('+++++++++++++') ;
end ;
/
create trigger t_b4ir
 before insert on t
begin
   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' insert on T') ;
   dbms_output.put_line ('+++++++++++++') ;
end ;
/
create trigger t_b4ur
 before update on t
begin
   dbms_output.put_line ('+++++++++++++') ;
   dbms_output.put_line (' for each row trigger') ;
   dbms_output.put_line (' update on T') ;
   dbms_output.put_line ('+++++++++++++') ;
end ;
/
set serveroutput on size 2000
select * from t ;
update t set c = 'ABD' where n = 1 ;
commit ;
select * from t ;
declare
   temp_lob clob ;
begin
   select c into temp_lob
    from t
    where n = 1
    for update ;
   dbms_lob.write (lob_loc => temp_lob,
                   amount => 1,
                   offset => 3,
                   buffer => 'E') ;
   commit ;
end ;
/
select * from t ;
 

--
http://www.freelists.org/webpage/oracle-l

Other related posts:

  • » changing lobs with select for update and dbms_lob.write - no triggers are fired?