测试DBLINK触发器事务_pg 触发器 dblink perform

STEP1:在数据库TW中创表
conn system/passwd@tw;
create user test identified by test;
grant connect to test;
grant create table to test;
alter user test quota 1M on users;
----------------------
conn test/test@tw;
create table test(name varchar2(20));
insert into test values('from_tw');

STEP2:在数据库TW1中创表
conn system/passwd@tw1;
create user test identified by test;
grant connect to test;
grant create table to test;
alter user test quota 1M on users;
grant create trigger to test;
--------------------------
conn test/test@tw1;
create table test(name varchar2(20));
insert into test values('from_tw1');

STEP3:在TW1中建立DBLINK与触发器
create public database link totw connect to test identified by test using 'TW';
---------------------
create or replace trigger write_tw_test
after insert on test
referencing old as old new as new
for each row
begin
 insert into test.test@totw(name) values(:new.name);
 dbms_output.put_line('---inserting---');
end;
/

STEP4:演示

STEP5:结论
触发器的操作与触发的事件属于同一个事务，触发器与触发事件一起提交、回滚(DBLINK一样)
行级触发器在某行发生变化时触发，只有触发器做完它的操作，后面的事件才会继续进行(等待触发器完成)

STEP6:测试DBLINK中触发器是否工作(TW)
create or replace trigger read_insert
after insert on test
for each row
begin
 insert into test1 values('read_from_tw1');
end;
/ --工作

--容易造成等待触发器操作全部完成，与等待锁