CREATE DATABASE LINK banklinkname --dblinkname--第一个bank为数据库用户名,第二个bank为数据库的密码--host为远程主机,port为端口-- SERVICE_NAME为本地服务器数据库的sid
CONNECT TO bank IDENTIFIED BY bank USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.33)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
commit;
--SELECT * FROM sys_menu@banklinkname;
create or replace
TRIGGER insert_cardinfo
after insert
on USER_INFOR_MESSAGE
--referencing old as old_value
-- new as new_value
for each row
DECLARE
--PRAGMA AUTONOMOUS_TRANSACTION;
newidnumber number;
newcardid VARCHAR2(12);
idserial VARCHAR2(20);
newmessagetype NUMBER;
begin
if inserting then
newmessagetype := :new.MESSAGETYPE;
if newmessagetype=0 then
newidnumber := :new.idnumber;
newcardid := :new.cardid;
idserial := :new.idserial;
insert into cardinfo@banklinkname (id,cardid,userid) values (newidnumber,newcardid,idserial);
elsif newmessagetype=3 then
newidnumber := :new.idnumber;
newcardid := :new.cardid;
idserial := :new.idserial;
insert into cardinfo@banklinkname (id,cardid,userid) values (newidnumber,newcardid,idserial);
end if;
end if;
end;
CONNECT TO bank IDENTIFIED BY bank USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.33)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
commit;
--SELECT * FROM sys_menu@banklinkname;
create or replace
TRIGGER insert_cardinfo
after insert
on USER_INFOR_MESSAGE
--referencing old as old_value
-- new as new_value
for each row
DECLARE
--PRAGMA AUTONOMOUS_TRANSACTION;
newidnumber number;
newcardid VARCHAR2(12);
idserial VARCHAR2(20);
newmessagetype NUMBER;
begin
if inserting then
newmessagetype := :new.MESSAGETYPE;
if newmessagetype=0 then
newidnumber := :new.idnumber;
newcardid := :new.cardid;
idserial := :new.idserial;
insert into cardinfo@banklinkname (id,cardid,userid) values (newidnumber,newcardid,idserial);
elsif newmessagetype=3 then
newidnumber := :new.idnumber;
newcardid := :new.cardid;
idserial := :new.idserial;
insert into cardinfo@banklinkname (id,cardid,userid) values (newidnumber,newcardid,idserial);
end if;
end if;
end;