Example 1

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
;