Add the new fields (customer_id, auth_code, and reference), and add a new index on the customer_id
field that reference an existing customer.
-- Adding the three new fields: customer_id, auth_code and reference.
ALTER TABLE `orders` ADD COLUMN `customer_id` INT,
ADD COLUMN `auth_code` VARCHAR(50),
ADD COLUMN `reference` VARCHAR(50);
-- Adding a new index to orders table
CREATE INDEX `idx_orders_customer_id` ON `orders` (`customer_id`);
5. Delete the old shopping_cart_create_order stored procedure, and create a new one by executing the
following code (don??™t forget to set the delimiter to $$):
-- Drop shopping_cart_create_order stored procedure
DROP PROCEDURE shopping_cart_create_order$$
-- Create shopping_cart_create_order stored procedure
CREATE PROCEDURE shopping_cart_create_order(IN inCartId CHAR(32),
IN inCustomerId INT)
BEGIN
DECLARE orderId INT;
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on, customer_id) VALUES (NOW(), inCustomerId);
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO orderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (order_id, product_id, attributes,
product_name, quantity, unit_cost)
SELECT orderId, p.product_id, sc.attributes, p.name, sc.quantity,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS unit_cost
FROM shopping_cart sc
INNER JOIN product p
ON sc.
Pages:
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656