请帮我改一下这个触发器
请各位帮我看一下这个触发器哪里写错了,该怎么改
现在有三张表,结构如下
---------------------采购进货/采购退货表-----------------------
CREATE TABLE WSSSELLERINOUT(
SIOID CHAR(14),
SIODATE DATE,
PRVID CHAR(4),
SIONEEDPAY NUMBER(10),
SIOPAID NUMBER(10),
SIOOWE NUMBER(10),
OPRTID CHAR(3),
STAID CHAR(3),
SISTATUS CHAR(1),
SIOTYPE CHAR(1),--这个字段代表是进货还是退货
CONSTRAINTS SELLERINOUT_SIOID_PK PRIMARY KEY(SIOID)
);
---------------------采购进货/采购退货表详细-----------------------
CREATE TABLE WSSSELLERINOUTDETAIL(
SIOID CHAR(14),--通过外键关联到采购进货/采购退货总表
MID CHAR(5),
STOID CHAR(2),
MAMOUNT NUMBER(3),
PRICE NUMBER(6),
SUMPRICE NUMBER(10),
CONSTRAINTS SELLERINOUTDETAIL_SIOMSTO_PK PRIMARY KEY(SIOID, MID, STOID)
);
-----------------------库存信息--------------------------
CREATE TABLE WSSSTORAGE(
MID CHAR(5),
STOAMOUNT NUMBER(3),
SELLSUM NUMBER(3),
PREINPRICE NUMBER(6),
AVGINPRICE NUMBER(6),
SELLSUMPRICE NUMBER(10),
STOSUMPRICE NUMBER(10),
STOID CHAR(2),
CONSTRAINTS STORAGE_MSTO_PK PRIMARY KEY(MID, STOID)
);
各个外键关联的语句没有放上来...
我想通过触发器实现在进货退货详细表中添加产品时在库存信息中也进行相应的修改,写了这么一个触发器,可是总是显示触发器无效未通过确认,不明白应该怎么改
--定义进货退货时在详单上插入数据后同步更新库存的触发器
CREATE OR REPLACE TRIGGER WSSSIOD_WSSSTO
AFTER INSERT ON WSSSELLERINOUTDETAIL
FOR EACH ROW
DECLARE
V_INOUT WSSSELLERINOUT.SIOTYPE%TYPE;
BEGIN
--首先判断是进货还是退货
SELECT IO.SIOTYPE
INTO V_INOUT
FROM WSSSELLERINOUT IO
WHERE IO.SIOID = :NEW.SIOID;
IF V_INOUT = '1' THEN
--进货时,若库存中有这件商品则库存量增加
UPDATE WSSSTORAGE STO
SET STO.STOAMOUNT = STO.STOAMOUNT + :NEW.MAMOUNT
WHERE STO.MID = :NEW.MID;
--如果库存里还没有这件商品就新加一条记录
IF SQL%NOTFOUND THEN
INSERT INTO WSSSTORAGE
VALUES(:NEW.MID,:NEW.MAMOUNT,0,:NEW.PRICE,0,0,0,:NEW.STOID);
END IF;
ELSIF V_INOUT = '0' THEN
--退货时不可能存在找不到退货商品,退货时库存减少
UPDATE WSSSTORAGE STO
SET STO.STOAMOUNT = STO.STOAMOUNT - :NEW.MAMOUNT;
END IF;
END;