CREATE TABLE `order` (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(64) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `order` VALUES
(NULL, 'Test order 1')
, (NULL, 'Test order 2')
, (NULL, 'Test order 3');
CREATE TABLE pos (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, order_id INT UNSIGNED NOT NULL
, name VARCHAR(64) NOT NULL
, amount SMALLINT NOT NULL
, price DECIMAL (6,2) NOT NULL
, status TINYINT NULL
) ENGINE = InnoDB;
INSERT INTO pos VALUES
(null, 1, 'Schrauben', 50, 0.10, 0)
, (null, 1, 'Muttern', 50, 0.10, 0)
, (null, 2, 'Nägel', 1000, 0.05, 1);
CREATE TABLE pos_shadow LIKE pos;
ALTER TABLE pos_shadow ENGINE = MEMORY;
INSERT INTO pos_shadow SELECT * FROM pos;
delimiter //
CREATE TRIGGER upd BEFORE UPDATE ON pos
FOR EACH ROW
BEGIN
UPDATE pos_shadow AS p
JOIN `order` AS o ON o.id = p.order_id and o.id = NEW.order_id
SET status = 1
WHERE o.id = 1;
END;
//
delimiter ;
SELECT o.name, p.name, p.amount, p.price, (p.amount*p.price) AS total, p. status
FROM `order` AS o
JOIN pos_shadow AS p ON p.order_id = o.id
;
UPDATE pos AS p
JOIN `order` AS o ON o.id = p.order_id AND o.id = 1
SET p.status = 1, p.order_id = 1
where o.id = 1;
SELECT o.name, p.name, p.amount, p.price, (p.amount*p.price) AS total, p. status
FROM `order` AS o
JOIN pos AS p on p.order_id = o.id
;
