mysql中触发器删除数据

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

-- 创建 order_details 表
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 创建 AFTER DELETE 触发器
DELIMITER $$
CREATE TRIGGER delete_order_details
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    DELETE FROM order_details WHERE order_id = OLD.order_id;
END$$
DELIMITER ;

-- 创建中间表
CREATE TABLE temp_delete_orders (
    order_id INT
);

-- 修改触发器，将需要删除的订单ID插入中间表
DELIMITER $$
CREATE TRIGGER delete_order_details
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO temp_delete_orders (order_id) VALUES (OLD.order_id);
END$$
DELIMITER ;

-- 创建一个事件调度器，定期清理中间表中的数据
DELIMITER $$
CREATE EVENT clean_temp_delete_orders
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    DELETE FROM order_details WHERE order_id IN (SELECT order_id FROM temp_delete_orders);
    DELETE FROM temp_delete_orders;
END$$
DELIMITER ;