我新建一张dblink配置信息表,存放dblink信息,
在这张表上建触发器,每当往表里面新增或者修改dblink信息时,
触发器执行删除所有dblink,然后再重新建,但是测试时,
触发器总是报ora-04092的错误,是不是触发器中不能包含creae语句?
我把删除创建dblink写成存储过程,然后用触发器调用,还是不行,请大侠帮忙解决^-^
create or replace trigger tri_dblink
after insert or update on t_sys_dblinkcfg
declare
v_sql varchar2(500);
begin for v in (select tnsname, susername, suserpwd, dblinkname
from t_sys_dblinkcfg) loop if p_global.isdblinkexists(v.dblinkname) then
execute immediate 'drop database link ' || v.dblinkname;
end if;
v_sql := 'create database link ' || v.dblinkname || ' connect to ' ||
v.susername || ' identified by ' || v.suserpwd ||
' using ''' || v.tnsname || '''';
execute immediate v_sql;
end loop;
end tri_dblink;
在这张表上建触发器,每当往表里面新增或者修改dblink信息时,
触发器执行删除所有dblink,然后再重新建,但是测试时,
触发器总是报ora-04092的错误,是不是触发器中不能包含creae语句?
我把删除创建dblink写成存储过程,然后用触发器调用,还是不行,请大侠帮忙解决^-^
create or replace trigger tri_dblink
after insert or update on t_sys_dblinkcfg
declare
v_sql varchar2(500);
begin for v in (select tnsname, susername, suserpwd, dblinkname
from t_sys_dblinkcfg) loop if p_global.isdblinkexists(v.dblinkname) then
execute immediate 'drop database link ' || v.dblinkname;
end if;
v_sql := 'create database link ' || v.dblinkname || ' connect to ' ||
v.susername || ' identified by ' || v.suserpwd ||
' using ''' || v.tnsname || '''';
execute immediate v_sql;
end loop;
end tri_dblink;
对自身表操作,要用自治事务...
用new值能不能实现呢
...
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
after update on t_sys_dblinkcfg
declare
v_sql varchar2(500);
pragma autonomous_transaction;
begin for v in (select tnsname, susername, suserpwd, dblinkname
from t_sys_dblinkcfg) loop
if p_global.isdblinkexists(v.dblinkname) then
execute immediate 'drop database link ' || v.dblinkname;
end if;
v_sql := 'create database link ' || v.dblinkname || ' connect to ' ||
v.susername || ' identified by ' || v.suserpwd ||
' using ''' || v.tnsname || '''';
execute immediate v_sql;
end loop;end tri_dblink;为什么第一次update,dblink没有重新建,第二次update的时候才删除新建了第一次修改的dblink
after update on t_sys_dblinkcfg
for each row
declare
v_sql varchar2(500);
pragma autonomous_transaction;
begin if p_global.isdblinkexists(:old.dblinkname) then
execute immediate 'drop database link ' || :old.dblinkname;
end if;
v_sql := 'create database link ' || :new.dblinkname || ' connect to ' ||
:new.susername || ' identified by ' || :new.suserpwd ||
' using ''' || :new.tnsname || '''';
execute immediate v_sql;end tri_dblink;