Create a trigger ord_upd, realize when to insert a row the lineitem table, according to the order number to modify the orders table order price, order the total price plus the amount of the goods detail, and verify that trigger
In the table statement
The create table orders
(
Orderid int the not null auto_increment primary key,
Userid char (6) not null,
Orderdate datetime not null, the current date! -
Totalprice decimal (10, 2),
Order status tinyint null - whether treatment 1 0 means to be processed! -
)
The create table lineitem
(
Orderid int the not null,
Itemid char (1) not null,
Quantity int the not null,
Unitprice decimal (10, 2) not null,
Primary key (orderid, itemid and quantity)
)
Insert into the orders (orderid, userid, orderdate, totalprice, status) VALUES
12:06:34 (20170911, 'u001', '2017-09-25', ', '0'),
15:04:45 (20170912, 'u002', '2017-09-25', ', '0'),
17:26:52 (20170913, 'u003', '2017-09-25', ', '0'),
18:07:04 (20170914, 'u003', '2017-09-25', ', '1'),
09:16:24 (20170915, 'u004', '2017-09-26', ', '0')
Insert into the 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, 'the RP - SN - 01', 2, '240'),
(20170915, 'the AV - SB - 02', 2, '50')
CodePudding user response:
Has been resolvedDelimiter//
Create the trigger ord_upd after insert on the lineitem for each row
The begin
Declare tota decimal (10, 2);
Select (quantity * unitprice) into tota from lineitem where orderid=new. Orderid.
Update the orders set totalprice=totalprice + tota where orderid=(select orderid from lineitem where orderid=new, orderid);
End//
Delimiter.
CodePudding user response:
