一共两个库,avatar(运行库)和avatarx(中间库),是通过dblink连接的,触发器是建立在中间库上的一个表上的。两个库的global_name的值都是true,如果是都设为FALSE的话触发器能编译成功,但是向中间表插入数据时,无法将数据传递给运行表。但是都设为TRUE时,触发器编译不成功,会报错。麻烦大家帮我看一下,谢谢了。触发器代码如下:
create or replace trigger Tri_pc_o_w_g after insert on PC_OIL_WELL_GATHER
2 --当PC_OIL_WELL_GATHER表中有insert命令时执行触发器
3 for each row
4 --遍历每一行
5 begin
6 insert into PC_OIL_WELL_OPERATION@haitadblink --向haitadb库中的PC_OIL_WELL_OPERATION表中插入下面的字段
7 (id,well_id,elec_pump_voltage_a,elec_pump_voltage_b,elec_pump_voltage_c,elec_pump_current_a,elec_pump_current_b,
8 elec_pump_current_c,up_current,down_current,max_current_rating,min_current_rating,max_load,min_load,power_active,power_factor,
9 power_consumption,tubing_pres,casing_pres,displacement_packets,current_packets,load_packets,power_packets,acquisition_time,
10 stroke_length,stroke_frequency)
11 select seq_pc_oil_well_operation.nextval,ht_well_cj_info.well_id,:new.elec_pump_voltage_a,:new.elec_pump_voltage_b,:new.elec_pump_voltage_c,:new.elec_pump_current_a,
12 :new.elec_pump_current_b,:new.elec_pump_current_c,:new.up_current,:new.down_current,:new.max_current_rating,
13 :new.min_current_rating,:new.max_load,:new.min_load,:new.power_active,:new.power_factor,:new.power_consumption,:new.tubing_pres,:new.casing_pres,:new.displacement_packets,:new.current_packets,:new.load_packets,:new.power_packets,:new.acquisition_time,
14 :new.stroke_length,:new.stroke_frequency
15 --well_id是通过cj_id关联haitadb库中的ht_well_cj_info表得到的
16 from ht_well_cj_info@haitadblink
17 where [email protected]_id=:new.cj_id;
18 end Tri_pc_o_w_g;报错信息如下:
SQL> show error
Errors for TRIGGER AVATARX.TRI_PC_O_W_G:
LINE/COL ERROR
--------
0/0 ORA-04052: 在查找远程对象 AVATAR.HT_WELL_CJ_INFO@HAITADBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM 时出错 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-02085: 数据库链接 HAITADBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM 连接到 HAITADBX.REGRESS.RDBMS.DEV.US.ORACLE.COM
create or replace trigger Tri_pc_o_w_g after insert on PC_OIL_WELL_GATHER
2 --当PC_OIL_WELL_GATHER表中有insert命令时执行触发器
3 for each row
4 --遍历每一行
5 begin
6 insert into PC_OIL_WELL_OPERATION@haitadblink --向haitadb库中的PC_OIL_WELL_OPERATION表中插入下面的字段
7 (id,well_id,elec_pump_voltage_a,elec_pump_voltage_b,elec_pump_voltage_c,elec_pump_current_a,elec_pump_current_b,
8 elec_pump_current_c,up_current,down_current,max_current_rating,min_current_rating,max_load,min_load,power_active,power_factor,
9 power_consumption,tubing_pres,casing_pres,displacement_packets,current_packets,load_packets,power_packets,acquisition_time,
10 stroke_length,stroke_frequency)
11 select seq_pc_oil_well_operation.nextval,ht_well_cj_info.well_id,:new.elec_pump_voltage_a,:new.elec_pump_voltage_b,:new.elec_pump_voltage_c,:new.elec_pump_current_a,
12 :new.elec_pump_current_b,:new.elec_pump_current_c,:new.up_current,:new.down_current,:new.max_current_rating,
13 :new.min_current_rating,:new.max_load,:new.min_load,:new.power_active,:new.power_factor,:new.power_consumption,:new.tubing_pres,:new.casing_pres,:new.displacement_packets,:new.current_packets,:new.load_packets,:new.power_packets,:new.acquisition_time,
14 :new.stroke_length,:new.stroke_frequency
15 --well_id是通过cj_id关联haitadb库中的ht_well_cj_info表得到的
16 from ht_well_cj_info@haitadblink
17 where [email protected]_id=:new.cj_id;
18 end Tri_pc_o_w_g;报错信息如下:
SQL> show error
Errors for TRIGGER AVATARX.TRI_PC_O_W_G:
LINE/COL ERROR
--------
0/0 ORA-04052: 在查找远程对象 AVATAR.HT_WELL_CJ_INFO@HAITADBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM 时出错 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-02085: 数据库链接 HAITADBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM 连接到 HAITADBX.REGRESS.RDBMS.DEV.US.ORACLE.COM
// *Cause: An error has occurred when trying to look up a remote object.
// *Action: Fix the error. Make sure the remote database system has run
// KGLR.SQL to create necessary views used for querying/looking up
// objects stored in the database.有没有运行kglr.sql来创建必须的视图?
如果本地的值为true ,则要求建立的dblink的名字与远程数据库的global_name 相同。
如果本地为FALSE,可以不同!
根据上述原则,重新弄一下试试!
通过select * from global_name 查看远程或本地机器的global_name!