在oracle中求一trigger

[快跑蜗牛哥]

已知表
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

[范佩西_11]

自己在添加完字段

create or replace tr_test before insert or update on DELIVERYSIZE
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;

[hebo2005]

CREATE OR REPLACE TRIGGER TR_DELIVERYA
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

[范佩西_11]

少个trigger

[范佩西_11]

直接这样

create or replace trigger tr_test before insert or update on DELIVERYSIZE
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 的

[范佩西_11]

你要完善，因为你第一个表还有其它字段。其他字段怎么处理的，还有主键是怎么生成的。

[juan0701]

在trigger 是不是不需要寫commit及rollback呢！

[YY_MM_DD]

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;

[YY_MM_DD]

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;

[sleepzzzzz]

create or replace trigger tr_test before insert or update on DELIVERYSIZE
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;

[codearts]

[Quote=引用 7 楼 juan0701 的回复:]
在trigger 是不是不需要寫commit及rollback呢！
[/Quote]

不需要，trriger里是不能commit的（好像是这样，但是自治事务的trigger可以)

[随风醉舞]

create or replace trigger tr_test
  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;

[快跑蜗牛哥]

結貼