以前很少接触触发器。需求是这样的，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语句修改坐标值。