oracle觸發器加條件判斷、dblink

標籤：

--新增基站同步給電池組資訊create or replace trigger a_b_test  after  insert or update or delete  on BJLT.BASESTATION   --REFERENCING NEW AS new_val OLD AS old_val --在這裡設定名字,然後可引用新值,舊值  for each row   when(new.isnode=0)begin   if inserting then      insert into BSMS.BS_INFO@TOBSMS_BETTERY_LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM(INFOID, INFONAME, GROUPID, ADDRESS, BUILDDATE, MAINTENANCER,            TEL, TEMPERATURE, RECTIFIERCUR,      OUTVOL, CREATETIME, SORTID, ONEOFFVOL, TWOOFFVOL, ISNODE, NODENUM, ONOFFPOWER, ONOFFPOWERMODEL,      POWERA, POWERB, POWERC, POWEROUT, POWERACUR, POWERBCUR, POWERCCUR, POWERAVOL, POWERBVOL,      POWERCVOL, DOOROPEN, HS, YANGAN, SHUIJIN, HONGWAI, KONGTIAO, VERID)    --  values (BID,BNAME,GROUPSID,      values (:new.ID,:new.NAME,:new.GROUPSID,     ‘1‘,sysdate,‘1‘,      ‘1‘,0,‘1‘,      1,sysdate,1,      1,1,1,      1,‘1‘,‘1‘,      -1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,0,-1,-1,-1,-1,      1);  end if;end a_b_test;

dblink情況下，新增本地表，同步遠程伺服器另一張表

參考自：http://www.blogjava.net/hhhaaawwwkkk/archive/2009/05/06/269251.html

參考內容：

---建立dblink,dblink_test名稱，（被同步資料庫的a_test）ST10766使用者名稱，ep密碼，ass100連接字串create public database link dblink_test　　 connect to ST10766 identified by ep　　 using ‘ass100‘;---刪除dblink----drop public database link dblink_test;----建立表create table a_test (id int,name varchar(20),pass varchar(20))select * from a_test;insert into a_test (id,name,pass) values (1,‘zzn‘,‘shanshan‘)insert into b_test (id,username,password) values(‘1‘,‘zxl‘,‘xiaolan‘)----在目的資料庫上，測試dblink，查詢的是來源資料庫的表select * from a_test@dblink_orc10; select * from a_test;----建立觸發器create or replace trigger a_b_test  after  insert or update or delete  on a_test  for each rowbegin  if deleting then      delete from b_test where id=:old.id;  end if;  if inserting then      insert into b_test(id,username,password)  //b_test表的欄位      values(:new.id,:new.name,:new.pass); //a_test表的欄位  end if;  if updating then     update b_test set username=:new.name,password=:new.pass where id=:old.id;  end if;end a_b_test;

 

oracle觸發器加條件判斷、dblink