Oracle 同步触发器.doc

配置数据库监听和.Net服务名一、A为主服务器，B为备份服务器（在B服务器安装oracle的时候重命名下数据库，避免和LMTIS重名而冲突）在A服务器上打开oracle自带工具Net Configuration Assistant1、新建LISTENERNEW监听器，端口默认1521，如下所示：SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:oracleproduct10.1.0Db_1) (PROGRAM = extproc) ) )LISTENERNEW = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = yy-44126be2998b)(PORT = 1521) ) ) )LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = yy-44126be2998b)(PORT = 1521) )SID_LIST_LISTENERNEW = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = LMTIS) (ORACLE_HOME = D:oracleproduct10.2.0db_1) (SID_NAME = LMTIS) ) )2、新建本地NET服务名配置，服务名为B服务器数据库名，主机名为B的IP。端口默认1521。这样，在A服务器oracleproduct10.1.0db_2NETWORKADMIN 下的tnsnames.ora中如下所示：LMES = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = LMES) ) )LMTIS = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = LMTIS) ) )配置完成，从A服务器上打开PLSQL Developer，就可以选择登录A的数据库和B的数据库了创建数据库连接，跨数据库建立视图二、创建A,B数据库连接，和表触发器（以下全部在A服务器数据库LMTIS中创建）1、创建database link 跨数据库建立视图 - Create database link create public database link DBLINKTEST connect to SYSTEM identified by newqt using LMES; -DBLINKTEST 触发器名称-SYSTEM 数据库用户名-newqt 数据库密码-LMES为远程数据库B的SID2、创建成功后可在sqlplus中查看异地数据库信息，执行 select * from TEST_STURECORDDBLINKTEST触发器：触发器名称：updatedb_TEST_STURECORD表名：TEST_STURECORD触发器含有的方法：insert or update or deletecreate or replace trigger updatedb_TEST_STURECORD before insert or update or delete on TEST_STURECORD for each rowbegin if updating then update TEST_STURECORDDBLINKTEST set STUR_ID =:New.STUR_ID, stur_where=:New.stur_where where STUR_ID=:Old.STUR_ID; end if; if inserting then insert into TEST_STURECORDDBLINKTEST(STUR_ID,STU_UNP_ID,STUR_STIME,stur_where,STUR_WORK,STUR_FTIME) values (:New.STUR_ID,:New.STU_UNP_ID,:New.STUR_STIME,:New.stur_where,:New.STUR_WORK,:New.STUR_FTIME); end if; if deleting then delete from TEST_STURECORDDBLINKTEST where STUR_ID=:Old.STUR_ID; end if;end;测试语句：select * from TEST_STURECORDDBLINKTEST; select * from TEST_STURECORD;insertinto TEST_STURECORD(STUR_ID,STU_UNP_ID,STUR_STIME,stur_where,STUR_WORK,STUR_FTIME) values (2,3,09:00,测试111,服务员11,15:00);com