求告知這題怎么做：：
創建觸發器ord_upd,實作當向lineitem表插入一行資料時，根據訂單號對orders表的訂單總價進行修改，訂單總價加上該商品明細的金額。并驗證觸發器
表中的陳述句
create table orders
(
orderid int not null auto_increment primary key,
userid char(6) not null ,
orderdate datetime not null , -- 當前日期!--
totalprice decimal(10,2),
status tinyint null -- 訂單是否處理 1表示處理 0表示待處理!--
)
create table lineitem
(
orderid int not null ,
itemid char(10) not null ,
quantity int not null ,
unitprice decimal(10,2) not null ,
primary key(orderid,itemid,quantity)
)
insert into orders (orderid,userid,orderdate,totalprice,status) VALUES
(20170911,'u001','2017-09-25 12:06:34','','0'),
(20170912,'u002','2017-09-25 15:04:45','','0'),
(20170913,'u003','2017-09-25 17:26:52','','0'),
(20170914,'u003','2017-09-25 18:07:04','','1'),
(20170915,'u004','2017-09-26 09:16:24','','0')
insert into lineitem (orderid,itemid,quantity,unitprice) values
(20170911,'FI-SW-01',10,'12'),
(20170911,'FI-SW-02',12,'20'),
(20170912,'K9-BD-01',2,'1600'),
(20170912,'K9-PO-02',1,'2600'),
(20170913,'K9-DL-01',1,'3500'),
(20170914,'RP-SN-01',2,'240'),
(20170915,'AV-SB-02',2,'50')
uj5u.com熱心網友回復：
已經解決delimiter//
create trigger ord_upd after insert on lineitem for each row
begin
declare tota decimal(10,2);
select (quantity * unitprice) into tota from lineitem where orderid = new.orderid;
update orders set totalprice =totalprice +tota where orderid = (select orderid from lineitem where orderid = new.orderid);
end//
delimiter;
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101055.html
標籤：MySQL
下一篇：Previous writer likely failed to write hdfs://... Failing because I am unlikely