oracle触发器跨库写入,oracle跨库数据库link及触发器

1、创建oracle跨库LINK

CREATE  PUBLIC DATABASE LINK BEYOND_DATABASE CONNECT TO tms IDENTIFIED BY tmsneu USING '(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = tms)

)

)';

commit;

2、创建触发器

create or replace TRIGGER TRIGGER_BEYOND

after INSERT ON datatrans.TEST_TRAGER

--after update  ON datatrans.TEST_TRAGER

BEGIN

INSERT INTO tms.T_DTO_TEST_DEPT@BEYOND_DATABASE(id) VALUES('测试跨库触发器');

END;

3、执行sql语句

insert into TEST_TRAGER(id) values('sadfkjadsklsajdf');

commit;

4、订单详情添加的跨库触发器：

create or replace TRIGGER TRIGGER_INVOICE_DETAIL

AFTER INSERT ON datatrans.T_ORDER_INVOICE_DETAIL

FOR EACH ROW

declare

RECEIVER_ID VARCHAR2(32);

RECEIVER_NAME VARCHAR2(512);

BILLCOUNT NUMBER(18,6);

TURNOVERBOXAMOUNT NUMBER(18,6);

TURNOVERBOXVOLUME NUMBER(18,6);

WHOLEAMOUNT NUMBER(18,6);

WHOLEVOLUME NUMBER(18,6);

INVOICE_PRICE VARCHAR2(32 BYTE);

BEGIN

--准备好门店编码、门店名称

select

o.SHOPCODE ,o.SHOPNAME ,o.BILLCOUNT ,o.TURNOVERBOXAMOUNT, o.TURNOVERBOXVOLUME, o.WHOLEAMOUNT,o.WHOLEVOLUME,o.RCV_INMONEY

INTO

RECEIVER_ID, RECEIVER_NAME, BILLCOUNT, TURNOVERBOXAMOUNT,TURNOVERBOXVOLUME,WHOLEAMOUNT,WHOLEVOLUME,INVOICE_PRICE

from T_ORDER_INVOICE o WHERE o.BILLNO=:new.BILLNO;

dbms_output.put_line(RECEIVER_ID);

--将配送单号、商品编码、序号、单位、配送数量插入至TMS订单详情表

--(原)订单编号、商品编码、商品序号、货品单位、货品数量

INSERT INTO TMS.T_TMS_ORDER_DETAIL@BEYOND_DATABASE

(ORDER_CODE,GOODS_CODE,GOODS_ONORDER_CODE,GOODS_UNIT,PRODUCT_NUMBER)

VALUES

(:new.BILLNO,:new.GDSINCODE,:new.GOODSLINENO,:new.BASEUNIT,:new.AMOUNT);

--订单表

INSERT INTO TMS.T_TMS_ORDER@BEYOND_DATABASE

(ORDER_CODE,ORDER_STATE,RECEIVER_ID,RECEIVER_NAME,INVOICENO)

VALUES

(:new.SENDBILLNO,'22',RECEIVER_ID,RECEIVER_NAME,:new.SENDBILLNO);

--出货单表

INSERT INTO TMS.T_TMS_ORDER_INVOICE@BEYOND_DATABASE

(INVOICENO,INVOICE_STATUS,SHOPCODE,SHOPNAME,BILLCOUNT,TURNOVERBOXAMOUNT,TURNOVERBOXVOLUME,WHOLEAMOUNT,WHOLEVOLUME,INVOICE_PRICE)

VALUES

(:new.SENDBILLNO,'22',RECEIVER_ID,RECEIVER_NAME,BILLCOUNT,TURNOVERBOXAMOUNT,TURNOVERBOXVOLUME,WHOLEAMOUNT,WHOLEVOLUME,INVOICE_PRICE);

update T_ORDER_INVOICE oi set oi.ON_STATUS ='2' where oi.BILLNO = :new.BILLNO;

END;

5、测试

DECLARE

BEGIN

insert into T_ORDER_INVOICE_DETAIL

(ON_STATUS,AMOUNT,SENDSTD,BASEUNIT,GOODSLINENO,GDSINCODE,DEPTCODE,SENDBILLNO,BILLNO,ID)

values

('22',10,1,1,'瓶','2222222','部门11','100100','2015080800000002',NULL);

rollback;

END;

6、出货单详情删除的跨库触发器：

create or replace TRIGGER TRIGGER_INVOICE_DETAIL_DELETE

--出货单详情删除

BEFORE DELETE ON datatrans.T_ORDER_INVOICE_DETAIL

FOR EACH ROW

BEGIN

--根据指定的商品编码删除订单详情

update TMS.T_TMS_ORDER_DETAIL@BEYOND_DATABASE set IS_DELETED='1' where GOODS_CODE=:new.GDSINCODE;

END;