1.创建仓库表
create table storehouse(
advid varchar2(10),
advname varchar2(20),
advprice number(5,1)
)
2.验证
select * from storehouse
3.我用的是TOAD把一个表的数据导入到仓库表
4.添加列
alter table storehouse add(storenum number(5))
5.创建触发器 当orderproductS表中有一个产品卖出 仓库表数量-1
CREATE TRIGGER STOREHOUSE_TRI
AFTER UPDATE OF PRODUCTSTATUS ON ORDERPRODUCTS
FOR EACH ROW
BEGIN
IF(:NEW.PRODUCTSTATUS='已配送') THEN
UPDATE STOREHOUSE SET STORENUM=(STORENUM-1) WHERE ADVID= :OLD.PRODUCTID;
DBMS_OUTPUT.PUT_LINE('操作成功');
END IF;
END;
6.如果创建错了可以删除
DROP TRIGGER STOREHOUSE_TRI
7.验证
SELECT * FROM ORDERPRODUCTS WHERE productid='24726319'
update orderproducts set productstatus='已配送' where orderproductid='24955349'
select * from orderproducts where orderno='0370200811150090'
select * from storehouse where advid='24726319'
create table storehouse(
advid varchar2(10),
advname varchar2(20),
advprice number(5,1)
)
2.验证
select * from storehouse
3.我用的是TOAD把一个表的数据导入到仓库表
4.添加列
alter table storehouse add(storenum number(5))
5.创建触发器 当orderproductS表中有一个产品卖出 仓库表数量-1
CREATE TRIGGER STOREHOUSE_TRI
AFTER UPDATE OF PRODUCTSTATUS ON ORDERPRODUCTS
FOR EACH ROW
BEGIN
IF(:NEW.PRODUCTSTATUS='已配送') THEN
UPDATE STOREHOUSE SET STORENUM=(STORENUM-1) WHERE ADVID= :OLD.PRODUCTID;
DBMS_OUTPUT.PUT_LINE('操作成功');
END IF;
END;
6.如果创建错了可以删除
DROP TRIGGER STOREHOUSE_TRI
7.验证
SELECT * FROM ORDERPRODUCTS WHERE productid='24726319'
update orderproducts set productstatus='已配送' where orderproductid='24955349'
select * from orderproducts where orderno='0370200811150090'
select * from storehouse where advid='24726319'