利用VIEW的TRIGGER技術來簡化FORM的開發

我們在開發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
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
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
WHERE AR_ID=:NEW.AR_ID;

INSERT INTO
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後，就可以了。