oracle触发器 表示 搞不定啊
建表
/*==============================================================*/
/* Table: CUSTOMER */
/*==============================================================*/
create table CUSTOMER (
"custid" VARCHAR2(10) not null,
"custname" VARCHAR2(22),
"custadd" VARCHAR2(20),
"custphone" VARCHAR2(12),
"custfax" VARCHAR2(12),
constraint PK_CUSTOMER primary key ("custid")
)
/
/*==============================================================*/
/* Table: ORD */
/*==============================================================*/
create table ORD (
"ordid" VARCHAR2(10) not null,
"custid" VARCHAR2(10),
"orddate" DATE,
"d_date" DATE,
constraint PK_ORD primary key ("ordid"),
constraint FK_ORD_FK_ORD_CU_CUSTOMER foreign key ("custid")
references CUSTOMER ("custid")
)
/
/*==============================================================*/
/* Table: PROSTOCK */
/*==============================================================*/
create table PROSTOCK (
"proid" VARCHAR2(10) not null,
"proname" VARCHAR2(20),
"stiqty" NUMBER(10) default 0,
"insqty" NUMBER(10) default 0,
constraint PK_PROSTOCK primary key ("proid")
)
/
/*==============================================================*/
/* Table: DELIVERY */
/*==============================================================*/
create table DELIVERY (
"delino" VARCHAR2(10) not null,
"ordid" VARCHAR2(10),
"proid" VARCHAR2(10),
"carno" VARCHAR2(10),
constraint PK_DELIVERY primary key ("delino"),
constraint FK_DELIVERY_FK_ORD_DE_ORD foreign key ("ordid")
references ORD ("ordid"),
constraint FK_DELIVERY_FK_PROSTO_PROSTOCK foreign key ("proid")
references PROSTOCK ("proid")
)
/
/*==============================================================*/
/* Index: FK_ORD_DELIVERY_FK */
/*==============================================================*/
create index FK_ORD_DELIVERY_FK on DELIVERY (
"ordid" ASC
)
/
/*==============================================================*/
/* Index: FK_PROSTOCK_DELIVERY_FK */
/*==============================================================*/
create index FK_PROSTOCK_DELIVERY_FK on DELIVERY (
"proid" ASC
)
/
/*==============================================================*/
/* Table: INVOICE */
/*==============================================================*/
create table INVOICE (
"invid" VARCHAR2(10) not null,
"ordid" VARCHAR2(10),
"money" NUMBER(10) default 0,
constraint PK_INVOICE primary key ("invid"),
constraint FK_INVOICE_FK_ORD_IN_ORD foreign key ("ordid")
references ORD ("ordid")
)
/
/*==============================================================*/
/* Index: FK_ORD_INVOICE_FK */
/*==============================================================*/
create index FK_ORD_INVOICE_FK on INVOICE (
"ordid" ASC
)
/
/*==============================================================*/
/* Table: INVOICE_PROSTOCK */
/*==============================================================*/
create table INVOICE_PROSTOCK (
"invid" VARCHAR2(10) not null,
"proid" VARCHAR2(10) not null,
"o_qty" NUMBER(10) default 0,
constraint PK_INVOICE_PROSTOCK primary key ("invid", "proid"),
constraint FK_INVOICE__INVOICE_P_INVOICE foreign key ("invid")
references INVOICE ("invid"),
constraint FK_INVOICE__INVOICE_P_PROSTOCK foreign key ("proid")
references PROSTOCK ("proid")
)
/
/*==============================================================*/
/* Index: INVOICE_PROSTOCK_FK */
/*==============================================================*/
create index INVOICE_PROSTOCK_FK on INVOICE_PROSTOCK (
"invid" ASC
)
/
/*==============================================================*/
/* Index: INVOICE_PROSTOCK2_FK */
/*==============================================================*/
create index INVOICE_PROSTOCK2_FK on INVOICE_PROSTOCK (
"proid" ASC
)
/
/*==============================================================*/
/* Index: FK_ORD_CUSTOMER_FK */
/*==============================================================*/
create index FK_ORD_CUSTOMER_FK on ORD (
"custid" ASC
)
/
/*==============================================================*/
/* Table: WIP */
/*==============================================================*/
create table WIP (
"wipid" VARCHAR2(10) not null,
"proid" VARCHAR2(10),
"w_qty" NUMBER(10) default 0,
constraint PK_WIP primary key ("wipid"),
constraint FK_WIP_FK_PROSTO_PROSTOCK foreign key ("proid")
references PROSTOCK ("proid")
)
/
/*==============================================================*/
/* Index: FK_PROSTOCK_WIP_FK */
/*==============================================================*/
create index FK_PROSTOCK_WIP_FK on WIP (
"proid" ASC
)
/
我尝试着写的触发器
CREATE OR REPLACE TRIGGER INVOICE_PROSTOCK_INSERT AFTER INSERT
ON INVOICE_PROSTOCK FOR EACH ROW
DECLARE
vCount NUMBER := 0;
BEGIN
SELECT stiqty INTO vCount FROM PROSTOCK WHERE proid = :NEW.proid;
IF vCount <= :NEW.o_qty THEN
UPDATE PROSTOCK SET insqty = :NEW.o_qty - stiqty WHERE proid = :NEW.proid;
UPDATE PROSTOCK SET stiqty = 0 WHERE proid = :NEW.proid;
ELSE
UPDATE PROSTOCK SET stiqty = stiqty - :NEW.o_qty WHERE proid = :NEW.proid;
END IF;
END;
期望的目的：
往INVOICE_PROSTOCK表插入数据时 根据插入的INVOICE_PROSTOCK.o_qty更新PROSTOCK表的stiqty或insqty
[解决办法]
--1你的脚本里头字段都是加了双引号的，因此严格区分大小写，所以触发器里面字段名也要加双引号
CREATE OR REPLACE TRIGGER INVOICE_PROSTOCK_INSERT AFTER INSERT
ON INVOICE_PROSTOCK FOR EACH ROW
DECLARE
vCount NUMBER := 0;
BEGIN
SELECT "stiqty" INTO vCount FROM PROSTOCK WHERE "proid" = :NEW."proid";
IF vCount <= :NEW."o_qty" THEN
UPDATE PROSTOCK SET "insqty" = :NEW."o_qty" - "stiqty" WHERE "proid" = :NEW."proid";
UPDATE PROSTOCK SET "stiqty" = 0 WHERE "proid" = :NEW."proid";
ELSE
UPDATE PROSTOCK SET "stiqty" = "stiqty" - :NEW."o_qty" WHERE "proid" = :NEW."proid";
END IF;
END;
/
--2、改你的建表脚本，把双引号都去掉，你的触发器不用改。