oracle触发器学习

以前很少接触触发器。需求是这样的，CMS_PRODUCT_BASE表中POI基本信息按照大类的不同，又将数据存在不同的大类表中，如大类为09的poi存储在CMS_PRODUCT_BASE_09表中。现在需要总表和各个分表中的数据要求保持一致。已经有一个触发器，功能是当像总表中插入一条数据时，自动的将该条信息保存到其对应的分表中。

需要完成两个触发器：

1 当总表中删除一条数据时，相应分表中也要删除该条数据。

2 当总表中修改一条数据时，相应分表中的数据也要修改。

完成的程序是这样的。

修改表内容：

create or replace trigger tri_cms_product_base_upd
  after update on cms_product_base
  for each row
declare
  new_table_name varchar2(50);
  sql_str varchar2(2000);

begin
  new_table_name:='cms_product_base_'||:new.big_type;
        
    sql_str:='update '||new_table_name||' b
       set b.name      = '''||:new.name||''',
           b.address   = '''||:new.address||''',
           b.telephone = '''||:new.telephone||''',
           b.key_type='''||:new.key_type||''',
           b.xy_coord.sdo_gtype='||:new.xy_coord.sdo_gtype||',
           b.xy_coord.sdo_srid='||:new.xy_coord.sdo_srid||',
           b.xy_coord.sdo_point.x='||:new.xy_coord.sdo_point.x||',
           b.xy_coord.sdo_point.y='||:new.xy_coord.sdo_point.y||',
           b.road_num='''||:new.road_num||''',
           b.road='''||:new.road||''',
           b.big_type='''||:new.big_type||''',
           b.contrycode='''||:new.contrycode||''',
           b.key_name='''||:new.key_name||''',
           b.province='''||:new.province||''',
           b.city='''||:new.city||''',
           b.area='''||:new.area||''',
           b.adcode='''||:new.adcode||''',
           b.pre='''||:new.pre||''',
           b.cp='''||:new.cp||''',
           b.name_pinyin='''||:new.name_pinyin||''',
           b.road_pinyin='''||:new.road_pinyin||''' where b.poiid = '''||:new.poiid||'''';
   
   execute immediate sql_str;
   

end tri_cms_product_base_upd;

删除表中记录：

create or replace trigger tri_cms_product_base_del
  after delete on cms_product_base  
  for each row
declare
   new_table_name varchar2(50);
   sql_str varchar2(2000);
begin
   new_table_name:='cms_product_base_'||:old.big_type;
  sql_str:='delete from '||new_table_name||' where poiid = '''||:old.poiid||'''';
   
   execute immediate sql_str;
  
end tri_cms_product_base_del;

写这两个trigger中遇到的问题：

1 sdo_geometry的处理，直接在动态sql语句中b.xy_coord='||:new.xy_coord||'，会报编译错误，说字符串连接有问题。||连接的参数个数有误。这种要分开每个字段写，就好了。

2 空格的问题，调试的时候把sql语句输出来，空格问题一眼就看出来啦。

3 单引号的问题。单引号用三个，’’’

4 trigger的调试，新建一个调试窗口，在调试程序中写触发的语句，就可以调试了。

 

2 继续上个问题：如果更改的字段是大类字段，那么需要在原表中删除这条记录，在新的大类表中添加这条数据。那么上面的写法就不能解决这个问题。

修改后为：

create or replace trigger tri_cms_product_base_upd
  after update on cms_product_base
  for each row
declare
  new_table_name varchar2(50);
  sql_str varchar2(2000);
  sql_str_del varchar2(2000);
  sql_str_ins varchar2(2000);
  old_table_name varchar2(50);

begin
  old_table_name:='cms_product_base_'||:old.big_type;
  new_table_name:='cms_product_base_'||:new.big_type;
  
           
    sql_str:='update '||new_table_name||' b
       set b.name      = '''||:new.name||''',
           b.address   = '''||:new.address||''',
           b.telephone = '''||:new.telephone||''',
           b.key_type='''||:new.key_type||''',
           b.xy_coord.sdo_gtype='||:new.xy_coord.sdo_gtype||',
           b.xy_coord.sdo_srid='||:new.xy_coord.sdo_srid||',
           b.xy_coord.sdo_point.x='||:new.xy_coord.sdo_point.x||',
           b.xy_coord.sdo_point.y='||:new.xy_coord.sdo_point.y||',
           b.road_num='''||:new.road_num||''',
           b.road='''||:new.road||''',
           b.big_type='''||:new.big_type||''',
           b.contrycode='''||:new.contrycode||''',
           b.key_name='''||:new.key_name||''',
           b.province='''||:new.province||''',
           b.city='''||:new.city||''',
           b.area='''||:new.area||''',
           b.adcode='''||:new.adcode||''',
           b.cp='''||:new.cp||''',
           b.name_pinyin='''||:new.name_pinyin||''',
           b.road_pinyin='''||:new.road_pinyin||''' where b.poiid = '''||:new.poiid||'''';
   
   if(old_table_name=new_table_name) 
   then 
   execute immediate sql_str;
   end if;
   
   if (old_table_name<>new_table_name)
     then
     sql_str_del:='delete from '||old_table_name||' where poiid = '''||:old.poiid||''''; 
     sql_str_ins:='insert into '||new_table_name||'(poiid,xy_coord) values ('''||:new.poiid||''',sdo_geometry(2001, 8307, sdo_point_type(0, 0, null), null, null))';
     execute immediate sql_str_del;
     execute immediate sql_str_ins;
     execute immediate sql_str;
     
     end if;

end tri_cms_product_base_upd;

如果不对sdo_geometry字段初始化，直接执行下面的插入语句的话，会报”原子null”的错误。故在插入时，初始化sdo_geometry字段。然后再用update语句修改坐标值。