关于触发器、存储过程和DBlink的综合运用

浏览数：22 / 时间：2015年06月12日

关于触发器、存储过程和DBlink的综合运用
需求描述：
需要在两个不同oracl数据库实例中进行数据逻辑处理。如果A实例中的表有新数据插入或者数据更新，那么在B实例中执行与之相关的存储过程。
先假设A数据用户中表TEST有变动，那么触发器触发调用实例B中的存储过程改写TEST_LOG表
A中操作如下：
1.建表
-------------------------------------------------------------------
create table TEST
(
T_ID   NUMBER(4),
T_NAME VARCHAR2(20),
T_AGE NUMBER(2),
T_SEX CHAR(1)
)；
-------------------------------------------------------------------
2.建立与B对应的DBLINK
-------------------------------------------------------------------
create database link INFOSYSTEM
   connect to infosystem identified by infosystem
   using ‘(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.249)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = ORCL)
     )
   )‘;
------------------------------------------------------------------
3.建立触发器
------------------------------------------------------------------
CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test
DECLARE
v_type VARCHAR2(15);
BEGIN
IF INSERTING THEN
v_type := ‘INSERT‘;
DBMS_OUTPUT.PUT_LINE(‘记录已经成功插入，并已记录到日志‘);
ELSIF UPDATING THEN
v_type := ‘UPDATE‘;
DBMS_OUTPUT.PUT_LINE(‘记录已经成功更新，并已记录到日志‘);
ELSIF DELETING THEN
v_type := ‘DELETE‘;
DBMS_OUTPUT.PUT_LINE(‘记录已经成功删除，并已记录到日志‘);
END IF;
my_pro@infosystem(v_type);
END;
----------------------------------------------------------------

B中操作如下：
1.建表
----------------------------------------------------------------
create table TEST_LOG
(
L_USER VARCHAR2(15),
L_TYPE VARCHAR2(15),
L_DATE VARCHAR2(30)
)；
----------------------------------------------------------------
2.建存储过程
注意一定要加上PRAGMA AUTONOMOUS_TRANSACTION;让这个存储过程有自治的事务控制，不然会影响A的事务控制
----------------------------------------------------------------
create or replace procedure my_pro(v_type varchar2)
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
INSERT INTO test_log VALUES(user,v_type,
        TO_CHAR(sysdate,‘yyyy-mm-dd hh24:mi:ss‘));
commit;
end;
---------------------------------------------------------------

最后我们在A中输入以下测试语句：
INSERT INTO test VALUES(101,‘zhao‘,22,‘M‘);
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;

SELECT * FROM test;
SELECT * FROM test_log@INFOSYSTEM;

结果可能如下：
TEST无数据
TEST_LOG数据
1 AAAPF0AALAAABq8AAA INFOSYSTEM DELETE 2009-06-12 13:45:30
2 AAAPF0AALAAABq8AAL INFOSYSTEM INSERT 2009-06-12 13:45:30
3 AAAPF0AALAAABq8AAM INFOSYSTEM UPDATE 2009-06-12 13:45:30