Question

oracle中触发器的问题

--进货表
create table stock (
document_id varchar2(20),
ware_id varchar2(10),
counts number(10),
price number(10,2),
constraints sk_pk_id primary key(document_id, ware_id)
);
--库存表
create table storage (
ware_id varchar2(10),
counts number(10),
constraints sto_pk_id primary key(ware_id)
);
--单据表
create table document (
document_id varchar2(20),
stamp varchar2(20),
constraints doc_pk_id primary key(document_id)
);
--财务报表
create table affairs (
stamp varchar2(20),
amounts number(10, 2),
constraints aff_pk_stamp primary key(stamp)
);
alter table stock
add constraints sto_fk_d_id foreign key(document_id)
references document(document_id);
alter table stock
add constraints sto_fk_w_id foreign key(ware_id)
references storage(ware_id);

alter table document
add constraints doc_fk_stamp foreign key(stamp)
references affairs(stamp);
--触发器
create or replace trigger biu_stock before insert or update
on stock for each row declare num int;
begin select count(*) into num from storage sto where sto.ware_id = :new.ware_id;
if(num > 0) then update storage set storage.counts = (storage.counts + :new.counts)
where storage.ware_id = :new.ware_id;
else insert into storage values(:new.ware_id, :new.counts);
end if; end;

create or replace trigger biu_stock2 before insert or update
on stock for each row declare num2 int;
begin select count(*) into num2 from document doc
where doc.document_id = :new.document_id;
if(num2 = 0) then
insert into document values(:new.document_id, '进货');
end if; end;

create or replace trigger biu_document after insert or update
on document for each row
declare num3 int; countmoney int;
begin select count(*) into num3 from affairs aff
where aff.stamp = :new.stamp;
select sum(money) into countmoney
from (select sto.counts * sto.price money from stock sto
where sto.document_id = :new.document_id) stock_moeny;
if(num3 > 0) then
update affairs set affairs.amounts = countmoney;
else if(num3 = 0) then
insert into affairs values(:new.stamp, countmoney);
end if; end if; end;要求当进货表中插入数据时能反应到库存表以及财务表，
现在就是库存表中的数据可以随进货表中的数据更新，
但财务表中的交易金额(amounts)一项始终为空？
求哪位大侠给小弟指点一二？
感激不尽！
速度越快，追加分越高！
一楼的说的有理，但是财务表中的amounts一项值始终为空该怎么解决？

Answer 1

CREATE TABLE WARE_STOCK (
VC_WARE_ID VARCHAR2(10),
VC_DOCUMENT_ID VARCHAR2(20),
VC_STOCK_UNIT_ID VARCHAR2(10) NOT NULL,
N_STOCK_COUNTS NUMBER(10) NOT NULL,
N_STOCK_PRICE NUMBER(10,2) NOT NULL,
N_STOCK_AMOUNT NUMBER(10,2),
D_STOCK_TIME DATE,
-- D_STOCK_TIME VARCHAR2(20),-- NOT NULL,
VC_STOCK_STORAGE_ID VARCHAR2(10) NOT NULL,
);

CREATE TABLE WARE_STORAGE (
VC_STORAGE_ID VARCHAR2(10),
VC_WARE_ID VARCHAR2(10),
N_STORAGE_COUNTS NUMBER(10),
N_WARNING_COUNTS NUMBER(10),
);

CREATE TABLE FINANCE (
VC_DOCUMENT_STAMP VARCHAR2(20),
N_BUSINESS_AMOUNT NUMBER(10,2),
);
CREATE TABLE DOCUMENT (
VC_DOCUMENT_ID VARCHAR2(20),
VC_DOCUMENT_STAMP VARCHAR2(20) NOT NULL,
N_DOCUMENT_AMOUNT NUMBER(10, 2)
);

CREATE OR REPLACE TRIGGER BI_WARE_STOCK
BEFORE INSERT
ON WARE_STOCK
FOR EACH ROW
DECLARE NUM INT;
D_NUM2 INT;
--该商品的报警库存
WARNING_COUNTS INT;
BEGIN
SELECT :NEW.N_STOCK_COUNTS * :NEW.N_STOCK_PRICE
INTO :NEW.N_STOCK_AMOUNT FROM DUAL;
SELECT WARE.N_WARE_WARNING_COUNTS INTO WARNING_COUNTS
FROM WARE WHERE WARE.VC_WARE_ID = :NEW.VC_WARE_ID;

--查看单据表中是否存在该单据号
SELECT COUNT(*) INTO D_NUM2 FROM DOCUMENT DOC
WHERE DOC.VC_DOCUMENT_ID = :NEW.VC_DOCUMENT_ID;

--更新库存信息
UPDATE WARE_STORAGE SET WARE_STORAGE.N_STORAGE_COUNTS =
WARE_STORAGE.N_STORAGE_COUNTS + :NEW.N_STOCK_COUNTS
WHERE WARE_STORAGE.VC_STORAGE_ID = :NEW.VC_STOCK_STORAGE_ID
AND WARE_STORAGE.VC_WARE_ID = :NEW.VC_WARE_ID;

--若单据表中没有该单，则插入改单，否则更新
IF(D_NUM2 = 0)
THEN
INSERT INTO DOCUMENT VALUES(:NEW.VC_DOCUMENT_ID, '进货', :NEW.N_STOCK_AMOUNT);
ELSE
UPDATE DOCUMENT DOC SET DOC.N_DOCUMENT_AMOUNT =
DOC.N_DOCUMENT_AMOUNT + :NEW.N_STOCK_AMOUNT
WHERE DOC.VC_DOCUMENT_ID = :NEW.VC_DOCUMENT_ID;
END IF;
END;

CREATE OR REPLACE TRIGGER BI_DOCUMENT
BEFORE INSERT
ON DOCUMENT
FOR EACH ROW
DECLARE NUM INT;
DOC_AMOUNT FLOAT;
BEGIN
SELECT COUNT(*) INTO NUM FROM FINANCE FIN
WHERE FIN.VC_DOCUMENT_STAMP = :NEW.VC_DOCUMENT_STAMP;
IF(NUM = 0)
THEN
INSERT INTO FINANCE VALUES(:NEW.VC_DOCUMENT_STAMP, :NEW.N_DOCUMENT_AMOUNT);
END IF;
END;
一个示例，仅供参考。

Answer 2

主要原因是调用biu_document 触发器时，没有commit，所以执行
select sum(money) into countmoney
from (select sto.counts * sto.price money from stock sto
where sto.document_id = :new.document_id) stock_moeny
时，返回的是null值。
触发器之间也不能传递参数，这里建议写存储过程吧
在biu_stock触发器中调用存储过程分别向document，affairs写数据。

Answer 3

update affairs set affairs.amounts = countmoney;
这一句还要加上 where affairs.stamp = :new.stamp吧，要不然把所有的row都更新了

还有你的amounts是两位小数，而countmoney是整数，这样不会有精确度的问题？