there are two insert statements in the
mysql trigger. How to implement the second inert with the result of the first insert
DELIMITER $$
USE `nightclub`$$
DROP TRIGGER /*!50032 IF EXISTS */ `create_histroy_room_statuses`$$
CREATE
/*!50017 DEFINER = "root"@"localhost" */
TRIGGER `create_histroy_room_statuses` BEFORE DELETE ON `room_statuses`
FOR EACH ROW
BEGIN
INSERT `nightclub`.histroy_room_statuses (
room_id,
note,
customer_number,
arrival_time,
room_game,
drinks,
booking_time,
estimated_time,
assistant_table,
visitor,
cadres_patrol,
salesman_tip,
master_tip,
princess_tip,
drink_total,
prs_tip_total,
towel_number,
consume,
pay_method,
princess_customer_tel,
princess_customer_name,
createdAt,
updatedAt,
booking_employee_id,
princess_id,
salesman_id,
master_id,
booking_customer_tel,
booking_customer_name,
state,
enter_time
) SELECT
room_id,
note,
customer_number,
arrival_time,
room_game,
drinks,
booking_time,
estimated_time,
assistant_table,
visitor,
cadres_patrol,
salesman_tip,
master_tip,
princess_tip,
drink_total,
prs_tip_total,
towel_number,
consume,
pay_method,
princess_customer_tel,
princess_customer_name,
createdAt,
updatedAt,
booking_employee_id,
princess_id,
salesman_id,
master_id,
booking_customer_tel,
booking_customer_name,
state,
enter_time
FROM `nightclub`.room_statuses
WHERE room_id = old.room_id;
INSERT INTO histroy_room_status_prs (
employee_id,
room_id,
createdAt,
updatedAt,
customer_name,
customer_tel,
isExemptFee,
charge,
work_time
) SELECT
employee_id,
room_id,
createdAt,
updatedAt,
customer_name,
customer_tel,
isExemptFee,
charge,
work_time
FROM
room_status_prs WHERE room_id=old.room_id;
END;
$$
DELIMITER ;histroy_room_status_prs table I set up a foreign key but can"t get the value returned by the first insert. How to implement the master? thank you