我們在開發FORM時,如是單 TABLE的,套用TEMPLATE.fmb,是不用單獨處理insert/update/delete等步聚的。
如是VIEW的話,則要對VIEW的TABLE寫處理的CODE,且較麻煩。不便 於維護。
如用VIEW的TRIGGER的話,則簡單且好維護
[@more@] create table a1 (id number,aname varchar2(4) )
create table b1 (id number,bname varchar2(4) )
create view c1 as
select a1.id,a1.aname,b1.bname
from a1,b1
where b1.id(+)=a1.id
insert into a1 values(1,'a1')
insert into a1 values(2,'a2')
insert into b1 values(1,'b1')
insert into b1 values(2,'b1')
select * from c1
CREATE OR REPLACE TRIGGER c1_insert
INSTEAD OF INSERT ON c1
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO a1
(id, aname)
VALUES (
:new.id,
:new.aname);
INSERT INTO b1 (id, bname)
VALUES (
:new.id,
:new.bname);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate a1 or b1');
END c1_insert;
commit
insert into c1 values(3,'a3','b3')
select * from b1
CREATE OR REPLACE TRIGGER c1_update
INSTEAD OF update ON c1
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
UPDATE a1
SET ANAME=:new.aname
WHERE ID=:NEW.ID;
UPDATE B1
SET BNAME=:new.Bname
WHERE ID=:NEW.ID;
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate a1 or b1');
END c1_update;
UPDATE C1
SET ANAME='A11',BNAME='1'
WHERE ID=1
SELECT * FROM A1
CREATE OR REPLACE TRIGGER c1_DELETE
INSTEAD OF DELETE ON c1
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
DELETE a1
WHERE ID=:NEW.ID;
DELETE B1
WHERE ID=:NEW.ID;
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate a1 or b1');
END c1_DELETE;
DELETE C1
WHERE ID=2
以下是實例。在寫PROD的同時,也更新VENDOR的
(兩個是不同SERVER上的DB,DB版本也不同)
CREATE OR REPLACE TRIGGER APPS.GOBO_MGR_INTERCOMPANY_AR_VD
INSTEAD OF DELETE ON GOBO_MGR_INTERCOMPANY_AR_V
DECLARE
BEGIN
DELETE gobo_mgr_intercompany_ar
WHERE AR_ID=:NEW.AR_ID;
DELETE mgreport.gobo_mgr_intercompany_ar@vendor
WHERE AR_ID=:NEW.AR_ID;
END gobo_mgr_intercompany_ar_VD;
/
CREATE OR REPLACE TRIGGER APPS.GOBO_MGR_INTERCOMPANY_AR_VI
INSTEAD OF INSERT ON GOBO_MGR_INTERCOMPANY_AR_V
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO gobo_mgr_intercompany_ar
(COMPANY_CODE ,
CUSTOMER_CODE ,
OU_ID ,
CUSTOMER_ID ,
PERIOD ,
INVOICE_NO ,
INVOICE_DATE ,
DUE_DATE ,
INVOICE_CURRENCT ,
OU_CURRENCY ,
TO_OU_CURRENCY_RATE ,
TO_USD_RATE ,
ORIGINAL_AMOUNT ,
REMAINING_AMOUNT ,
FETCH_DATE ,
OU_CURRENCY_PRECISION ,
USD_PERCISION ,
OE_HEADER_ID ,
OE_LINE_ID ,
OE_ORDER_NUMBER ,
SHIP_NO ,
ORIGINAL_AMOUNT_OLD ,
REMAINING_AMOUNT_OLD ,
AR_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE )
VALUES (
:NEW.COMPANY_CODE,
:NEW.CUSTOMER_CODE,
:NEW.OU_ID ,
:NEW.CUSTOMER_ID ,
:NEW.PERIOD,
:NEW.INVOICE_NO,
:NEW.INVOICE_DATE,
:NEW.DUE_DATE,
:NEW.INVOICE_CURRENCT,
:NEW.OU_CURRENCY,
:NEW.TO_OU_CURRENCY_RATE,
:NEW.TO_USD_RATE,
:NEW.ORIGINAL_AMOUNT,
:NEW.REMAINING_AMOUNT,
:NEW.FETCH_DATE,
:NEW.OU_CURRENCY_PRECISION,
:NEW.USD_PERCISION,
:NEW.OE_HEADER_ID,
:NEW.OE_LINE_ID,
:NEW.OE_ORDER_NUMBER,
:NEW.SHIP_NO,
:NEW.ORIGINAL_AMOUNT_OLD,
:NEW.REMAINING_AMOUNT_OLD,
:new.aR_id,
:NEW.LAST_UPDATE_DATE,
:NEW.LAST_UPDATED_BY,
:NEW.LAST_UPDATE_LOGIN,
:NEW.CREATION_DATE
);
INSERT INTO mgreport.gobo_mgr_intercompany_ar@vendor
select * from gobo_mgr_intercompany_ar
where ar_id=:new.aR_id;
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate DATA');
END gobo_mgr_intercompany_ar_VI;
/
CREATE OR REPLACE TRIGGER APPS.GOBO_MGR_INTERCOMPANY_AR_VU
INSTEAD OF UPDATE ON GOBO_MGR_INTERCOMPANY_AR_V
DECLARE
BEGIN
UPDATE gobo_mgr_intercompany_ar
SET
COMPANY_CODE = :NEW.COMPANY_CODE,
CUSTOMER_CODE = :NEW.CUSTOMER_CODE,
OU_ID = :NEW.OU_ID,
CUSTOMER_ID = :NEW.CUSTOMER_ID,
PERIOD = :NEW.PERIOD,
INVOICE_NO = :NEW.INVOICE_NO,
INVOICE_DATE = :NEW.INVOICE_DATE,
DUE_DATE = :NEW.DUE_DATE,
INVOICE_CURRENCT = :NEW.INVOICE_CURRENCT,
OU_CURRENCY = :NEW.OU_CURRENCY,
TO_OU_CURRENCY_RATE = :NEW.TO_OU_CURRENCY_RATE,
TO_USD_RATE = :NEW.TO_USD_RATE,
ORIGINAL_AMOUNT = :NEW.ORIGINAL_AMOUNT,
REMAINING_AMOUNT = :NEW.REMAINING_AMOUNT,
FETCH_DATE = :NEW.FETCH_DATE,
OU_CURRENCY_PRECISION = :NEW.OU_CURRENCY_PRECISION,
USD_PERCISION = :NEW.USD_PERCISION,
OE_HEADER_ID = :NEW.OE_HEADER_ID,
OE_LINE_ID = :NEW.OE_LINE_ID,
OE_ORDER_NUMBER = :NEW.OE_ORDER_NUMBER,
SHIP_NO = :NEW.SHIP_NO,
ORIGINAL_AMOUNT_OLD = :NEW.ORIGINAL_AMOUNT_OLD,
REMAINING_AMOUNT_OLD = :NEW.REMAINING_AMOUNT_OLD,
LAST_UPDATE_DATE = :NEW.LAST_UPDATE_DATE,
LAST_UPDATED_BY = :NEW.LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = :NEW.LAST_UPDATE_LOGIN,
CREATION_DATE = :NEW.CREATION_DATE
WHERE AR_ID=:NEW.AR_ID;
DELETE mgreport.gobo_mgr_intercompany_ar@vendor
WHERE AR_ID=:NEW.AR_ID;
INSERT INTO mgreport.gobo_mgr_intercompany_ar@vendor
select * from gobo_mgr_intercompany_ar
where ar_id=:new.aR_id;
END gobo_mgr_intercompany_ar_VU;
/
註:如跨DB的TRIGGER編譯不成功,要相DB上是不是有BUG。
本人就遇到了這個情況,DBA把BUG做FIX後,就可以了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92289/viewspace-995383/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/92289/viewspace-995383/