mysql触发器级联删除

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

-- 创建 order_items 表
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
);

-- 插入示例数据
INSERT INTO orders (order_id, customer_name) VALUES (1, 'Alice');
INSERT INTO order_items (item_id, order_id, product_name) VALUES (1, 1, 'Product A');
INSERT INTO order_items (item_id, order_id, product_name) VALUES (2, 1, 'Product B');

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

-- 删除订单并触发级联删除
DELETE FROM orders WHERE order_id = 1;