已知表
create table DELIVERYA
(
DELY_NO VARCHAR2(14) not null,
DELY_ITEM NUMBER(3) not null,
PUR_NO CHAR(14) not null,
ITEM_NO NUMBER(4) not null,
MAT_NO VARCHAR2(14),
MODEL_NO VARCHAR2(40),
ORDER_NO VARCHAR2(20),
UNIT VARCHAR2(10),
CUST_UNIT VARCHAR2(10),
ART_QTY NUMBER(10,2) not null,
DELY_QTY NUMBER(18,9) not null,
NET_WT NUMBER(13,6),
CREATE_MAN VARCHAR2(20) not null,
CREATE_TIME DATE not null,
UP_MAN VARCHAR2(20),
UP_TIME DATE,
MAT_NAME VARCHAR2(820),
VENDOR CHAR(5) not null,
CHECK_STATUS CHAR(1) default 'N' not null,
SIM_NAME VARCHAR2(20) not null,
APLY_TYPE VARCHAR2(6) not null,
PUR_QTY NUMBER(18,9) not null,
TOTAL_WT NUMBER(18,6),
CHECK_TIME DATE,
CHECK_MAN VARCHAR2(10),
GOODS_NAME VARCHAR2(60)
)
其中 DELY_NO+DELY_ITEM 為主鍵
另一表:
create table DELIVERYSIZE
(
DELY_NO CHAR(14) not null,
DELY_ITEM NUMBER(2) not null,
SHOE_WITH VARCHAR2(5) not null,
SIZE1 NUMBER(5,1) default '0',
SIZE2 NUMBER(5,1) default '0',
SIZE3 NUMBER(5,1) default '0',
SIZE4 NUMBER(5,1) default '0',
SIZE5 NUMBER(5,1) default '0',
SIZE6 NUMBER(5,1) default '0',
SIZE7 NUMBER(5,1) default '0',
SIZE8 NUMBER(5,1) default '0',
SIZE9 NUMBER(5,1) default '0',
SIZE10 NUMBER(5,1) default '0',
SIZE11 NUMBER(5,1) default '0',
SIZE12 NUMBER(5,1) default '0',
SIZE13 NUMBER(5,1) default '0',
SIZE14 NUMBER(5,1) default '0',
SIZE15 NUMBER(5,1) default '0',
SIZE16 NUMBER(5,1) default '0',
SIZE17 NUMBER(5,1) default '0',
SIZE18 NUMBER(5,1) default '0',
SIZE19 NUMBER(5,1) default '0',
SIZE20 NUMBER(5,1) default '0',
SIZE21 NUMBER(5,1) default '0',
SIZE22 NUMBER(5,1) default '0',
SIZE23 NUMBER(5,1) default '0',
SIZE24 NUMBER(5,1) default '0',
SIZE25 NUMBER(5,1) default '0',
SIZE26 NUMBER(5,1) default '0',
SIZE27 NUMBER(5,1) default '0',
SIZE28 NUMBER(5,1) default '0',
SIZE29 NUMBER(5,1) default '0',
SIZE30 NUMBER(5,1) default '0',
NET1 NUMBER(5,1),
NET2 NUMBER(5,1),
NET3 NUMBER(5,1),
NET4 NUMBER(5,1),
NET5 NUMBER(5,1),
NET6 NUMBER(5,1),
NET7 NUMBER(5,1),
NET8 NUMBER(5,1),
NET9 NUMBER(5,1),
NET10 NUMBER(5,1),
NET11 NUMBER(5,1),
NET12 NUMBER(5,1),
NET13 NUMBER(5,1),
NET14 NUMBER(5,1),
NET15 NUMBER(5,1),
NET16 NUMBER(5,1),
NET17 NUMBER(5,1),
NET18 NUMBER(5,1),
NET19 NUMBER(5,1),
NET20 NUMBER(5,1),
NET21 NUMBER(5,1),
NET22 NUMBER(5,1),
NET23 NUMBER(5,1),
NET24 NUMBER(5,1),
NET25 NUMBER(5,1),
NET26 NUMBER(5,1),
NET27 NUMBER(5,1),
NET28 NUMBER(5,1),
NET29 NUMBER(5,1),
NET30 NUMBER(5,1)
)
其中 DELY_NO+DELY_ITEM 為主鍵
當DELIVERYSIZE表,修改或插入資料時,
DELIVERYA表中的TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30
create table DELIVERYA
(
DELY_NO VARCHAR2(14) not null,
DELY_ITEM NUMBER(3) not null,
PUR_NO CHAR(14) not null,
ITEM_NO NUMBER(4) not null,
MAT_NO VARCHAR2(14),
MODEL_NO VARCHAR2(40),
ORDER_NO VARCHAR2(20),
UNIT VARCHAR2(10),
CUST_UNIT VARCHAR2(10),
ART_QTY NUMBER(10,2) not null,
DELY_QTY NUMBER(18,9) not null,
NET_WT NUMBER(13,6),
CREATE_MAN VARCHAR2(20) not null,
CREATE_TIME DATE not null,
UP_MAN VARCHAR2(20),
UP_TIME DATE,
MAT_NAME VARCHAR2(820),
VENDOR CHAR(5) not null,
CHECK_STATUS CHAR(1) default 'N' not null,
SIM_NAME VARCHAR2(20) not null,
APLY_TYPE VARCHAR2(6) not null,
PUR_QTY NUMBER(18,9) not null,
TOTAL_WT NUMBER(18,6),
CHECK_TIME DATE,
CHECK_MAN VARCHAR2(10),
GOODS_NAME VARCHAR2(60)
)
其中 DELY_NO+DELY_ITEM 為主鍵
另一表:
create table DELIVERYSIZE
(
DELY_NO CHAR(14) not null,
DELY_ITEM NUMBER(2) not null,
SHOE_WITH VARCHAR2(5) not null,
SIZE1 NUMBER(5,1) default '0',
SIZE2 NUMBER(5,1) default '0',
SIZE3 NUMBER(5,1) default '0',
SIZE4 NUMBER(5,1) default '0',
SIZE5 NUMBER(5,1) default '0',
SIZE6 NUMBER(5,1) default '0',
SIZE7 NUMBER(5,1) default '0',
SIZE8 NUMBER(5,1) default '0',
SIZE9 NUMBER(5,1) default '0',
SIZE10 NUMBER(5,1) default '0',
SIZE11 NUMBER(5,1) default '0',
SIZE12 NUMBER(5,1) default '0',
SIZE13 NUMBER(5,1) default '0',
SIZE14 NUMBER(5,1) default '0',
SIZE15 NUMBER(5,1) default '0',
SIZE16 NUMBER(5,1) default '0',
SIZE17 NUMBER(5,1) default '0',
SIZE18 NUMBER(5,1) default '0',
SIZE19 NUMBER(5,1) default '0',
SIZE20 NUMBER(5,1) default '0',
SIZE21 NUMBER(5,1) default '0',
SIZE22 NUMBER(5,1) default '0',
SIZE23 NUMBER(5,1) default '0',
SIZE24 NUMBER(5,1) default '0',
SIZE25 NUMBER(5,1) default '0',
SIZE26 NUMBER(5,1) default '0',
SIZE27 NUMBER(5,1) default '0',
SIZE28 NUMBER(5,1) default '0',
SIZE29 NUMBER(5,1) default '0',
SIZE30 NUMBER(5,1) default '0',
NET1 NUMBER(5,1),
NET2 NUMBER(5,1),
NET3 NUMBER(5,1),
NET4 NUMBER(5,1),
NET5 NUMBER(5,1),
NET6 NUMBER(5,1),
NET7 NUMBER(5,1),
NET8 NUMBER(5,1),
NET9 NUMBER(5,1),
NET10 NUMBER(5,1),
NET11 NUMBER(5,1),
NET12 NUMBER(5,1),
NET13 NUMBER(5,1),
NET14 NUMBER(5,1),
NET15 NUMBER(5,1),
NET16 NUMBER(5,1),
NET17 NUMBER(5,1),
NET18 NUMBER(5,1),
NET19 NUMBER(5,1),
NET20 NUMBER(5,1),
NET21 NUMBER(5,1),
NET22 NUMBER(5,1),
NET23 NUMBER(5,1),
NET24 NUMBER(5,1),
NET25 NUMBER(5,1),
NET26 NUMBER(5,1),
NET27 NUMBER(5,1),
NET28 NUMBER(5,1),
NET29 NUMBER(5,1),
NET30 NUMBER(5,1)
)
其中 DELY_NO+DELY_ITEM 為主鍵
當DELIVERYSIZE表,修改或插入資料時,
DELIVERYA表中的TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30
for each row
begin
if inserting then insert into DELIVERYA(TOTAL_WT) values(SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30) where DELY_NO =:new.DELY_NO
and DELY_ITEM =:new.DELY_ITEM;
end if;
if updating then update DELIVERYA set TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30 where DELY_NO =:new.DELY_NO
and DELY_ITEM =:new.DELY_ITEM;
end if;
end;
AFTER INSERT OR UPDATE ON DELIVERYA FOR EACH ROW
BEGIN
update DELIVERYA
set TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30
where
commit;
end
for each row
begin update DELIVERYA set TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30 where DELY_NO =:new.DELY_NO
and DELY_ITEM =:new.DELY_ITEM;
end if;
end;
hebo2005 的不行
我再試試linzhangs 的
create or replace trigger mytrigger
before insert or update on DELIVERYSIZE
for each row
begin
if updating or inserting then
update DELIVERYA set TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30
where DELY_NO =:new.DELY_NO and DELY_ITEM =:new.DELY_ITEM;
end if;
create or replace trigger mytrigger
before insert or update on DELIVERYSIZE
for each row
begin
if updating or inserting then
update DELIVERYA set TOTAL_WT=SIZE1*NET1+SIZE2*NET2+SIZE3*NET3+...+SIZE30*NET30
where DELY_NO =:new.DELY_NO and DELY_ITEM =:new.DELY_ITEM;
end if;
end;
for each row
begin
--下面这行,注意取值:new.
update DELIVERYA set TOTAL_WT=:new.SIZE1*:new.SNET1+:new.SSIZE2*:new.SNET2+...+:new.SSIZE30*:new.SNET30
where DELY_NO =:new.DELY_NO and DELY_ITEM =:new.DELY_ITEM;
end if;
end;
不需要,trriger里是不能commit的(好像是这样,但是自治事务的trigger可以)
before insert or update on DELIVERYSIZE
for each row
begin
update DELIVERYA
set TOTAL_WT = SIZE1 * NET1 + SIZE2 * NET2 + SIZE3 * NET3 +
SIZE4 * NET4 + SIZE5 * NET5 + SIZE6 * NET6 +
SIZE7 * NET7 + SIZE8 * NET8 + SIZE9 * NET9 +
SIZE10 * NET10 + SIZE11 * NET11 + SIZE12 * NET12 +
SIZE13 * NET13 + SIZE14 * NET14 + SIZE15 * NET15 +
SIZE16 * NET16 + SIZE17 * NET17 + SIZE18 * NET18 +
SIZE19 * NET19 + SIZE20 * NET20 + SIZE21 * NET21 +
SIZE22 * NET22 + SIZE23 * NET23 + SIZE24 * NET24 +
SIZE25 * NET25 + SIZE26 * NET26 + SIZE27 * NET27 +
SIZE28 * NET28 + SIZE29 * NET29 + SIZE30 * NET30
where DELY_NO = :new.DELY_NO
and DELY_ITEM = :new.DELY_ITEM;
end if;
end;