CREATE OR REPLACE TRIGGER sbo_c_case_info_look_update  INSTEAD OF update  ON sbo_c_case_info_look
                 REFERENCING NEW AS NEW OLD AS OLD

 
declare
  v_user_id number(10);
  v_forum_id number(10);
  v_blob blob;
    v_blob1 blob;--子串
  v_post_id number(10);
  v_topic_id number(10);

  directions blob;
  amount integer;
  offset2 integer;
 
  v_blob_maxlength integer;--每次读取的最大长度，赋予常量
  v_index integer;--索引
  v_sub_count integer;--子串的个数
  v_cursor integer;--游标
  v_temp_len integer;--临时长度值
BEGIN

       select user_id into v_user_id from jforum_users ju where ju.userid_master = (select c.userid from pub_t_bu_image bb,secu_t_user c
        where :new.FILEDEAL = bb.datakey and bb.upuserid = c.userid);--获得对应的用户id

         select jb.vblob into v_blob from (select bb.vblob from sbo_c_case_info_look a,pub_t_bu_image bb,secu_t_user c
           where a.FILEDEAL = bb.datakey and bb.upuserid = c.userid and a.datakey=:new.datakey) jb;--获得典型案例内容

    
        select jf.forum_id into v_forum_id from jforum_forums jf where jf.forum_name = '典型案例';
   if :new.IFPUBLISH=1 and :new.IFPUBLISH<>:old.IFPUBLISH  then
          insert into jforum_topics(topic_id,forum_id,topic_title,datakey,user_id) values(jforum_topics_seq.nextval,v_forum_id,:new.CASETITLE,:old.datakey,v_user_id);
          insert into jforum_posts(post_id,topic_id,forum_id,user_id,poster_ip,post_time,post_edit_time,enable_html) values(jforum_posts_seq.nextval,jforum_topics_seq.currval,v_forum_id,v_user_id,'127.0.0.1',sysdate,sysdate,0);
          
          select jforum_topics_seq.currval into v_topic_id from dual;
          select jforum_posts_seq.currval into v_post_id from dual;--获得post_id
          
          
          --select UTL_RAW.cast_to_varchar2(v_blob) into v_string from dual;
          update jforum_topics jt set jt.topic_first_post_id=v_post_id,jt.topic_last_post_id=v_post_id where jt.topic_id=v_topic_id;--更新两个字段
          
    1，      insert into jforum_posts_text(post_id,post_subject,post_text) values(v_post_id,:new.CASETITLE,EMPTY_BLOB());      --更新和新增一样要将blob字段设置为empty_blob()
    2，      select post_text into directions from jforum_posts_text  where post_id=v_post_id for update; --一定要用for update锁住记录，否则
                                                                                                                         --dbms_lob.open会出错

    3，      dbms_lob.open(directions, dbms_lob.lob_readwrite);
              
             v_blob_maxlength:=1000;--允许的长度
              amount := lengthb(v_blob); --blob的总长度
              v_sub_count :=amount/v_blob_maxlength+1;--分成子串的个数
              v_index :=1;--索引从1开始
              offset2 := 1; --begin writing to the first character of the clob
            
    4，          while v_index < v_sub_count  
               loop
                 v_cursor:=v_index*v_blob_maxlength;
 
                 DBMS_LOB.read(v_blob,v_blob_maxlength,offset2,v_blob1);
                
                 v_temp_len:= lengthb(v_blob1);
                dbms_lob.writeappend(directions,v_temp_len,v_blob1);

                 
                 v_index:=v_index+1;      
                 offset2:=v_cursor+1;
                 
              end loop;
              
  5，           if offset2<amount then
              
               v_temp_len:=amount-offset2;
               DBMS_LOB.read(v_blob,v_temp_len,offset2,v_blob1);
 
               dbms_lob.writeappend(directions,lengthb(v_blob1),v_blob1);
   
                end if;
  6，           dbms_lob.close(directions);
             
    elsif :new.IFPUBLISH=0 and :new.IFPUBLISH<>:old.IFPUBLISH then
          delete from jforum_posts_text pt where pt.post_id in (select p.post_id from jforum_posts p where p.topic_id IN (select jt.topic_id from jforum_topics jt where jt.datakey=:new.datakey));
          delete from jforum_posts jp where jp.topic_id in (select jt.topic_id from jforum_topics jt where jt.datakey=:new.dat