2. 创建触发器：实现在供应商表 Supplier 中删除供货商时，同时删除该供 货商所供应的商品信息及商品的销售记录。

CREATE TRIGGER delete_supplier_trigger
AFTER DELETE ON Supplier
FOR EACH ROW
BEGIN
DELETE FROM Product WHERE supplier_id = OLD.supplier_id;
DELETE FROM Sales WHERE product_id IN (SELECT product_id FROM Product WHERE supplier_id = OLD.supplier_id);
END;

注解：

- AFTER DELETE ON Supplier：表示在 Supplier 表执行删除操作之后触发该触发器。
- FOR EACH ROW：表示对于每一行执行一次触发器。
- OLD.supplier_id：表示当前被删除的供货商的 ID。
- DELETE FROM Product WHERE supplier_id = OLD.supplier_id：表示删除该供货商所供应的商品信息。
- DELETE FROM Sales WHERE product_id IN (SELECT product_id FROM Product WHERE supplier_id = OLD.supplier_id)：表示删除与该供货商所供应的商品相关的销售记录。

相关问题

-- 创建顾客表： CREATE TABLE Customer ( Customer_id VARCHAR(50) PRIMARY KEY, Customer_name VARCHAR(50), Customer_gender VARCHAR(10), Customer_phone VARCHAR(20), Customer_address VARCHAR(100) ); -- 创建商品表： CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(50), Product_type VARCHAR(20), Product_price DECIMAL(10, 2), stock_num INT ); -- 创建员工表： CREATE TABLE Employee ( Employee_id INT PRIMARY KEY, Employee_name VARCHAR(50), Employee_gender VARCHAR(10), Employee_phone VARCHAR(20), Employee_position VARCHAR(50) ); -- 创建供货商表： CREATE TABLE Supplier ( Supplier_id VARCHAR(50) PRIMARY KEY, Product_list VARCHAR(50), Supplier_name VARCHAR(50), Supplier_contact VARCHAR(50), Supplier_phone VARCHAR(20), Supplier_address VARCHAR(100) ); -- 创建销售表： CREATE TABLE Sale ( Sale_id INT AUTO_INCREMENT PRIMARY KEY, Product_id INT, Order_id INT, Order_date DATE, Employee_id INT, Customer_id VARCHAR(50), Sale_num INT, Sale_amount DECIMAL(10, 2), FOREIGN KEY (Product_id) REFERENCES Product (Product_id), FOREIGN KEY (Employee_id) REFERENCES Employee (Employee_id), FOREIGN KEY (Customer_id) REFERENCES Customer (Customer_id) ); -- 创建库存表： CREATE TABLE Stock ( Product_id VARCHAR(50) PRIMARY KEY, Stock_num INT, Supplier_id VARCHAR(50), indata DATE ); -- 创建商品与供货商的关系表 CREATE TABLE Product_Supplier ( Supplier_id VARCHAR(50) NOT NULL, Product_id VARCHAR(50) NOT NULL, PRIMARY KEY (Product_id, Supplier_id) ); -- 创建订单表： CREATE TABLE Order_dd ( Order_id INT AUTO_INCREMENT PRIMARY KEY, Product_id INT, Product_num INT, Customer_id VARCHAR(50), Order_date DATE, Order_price DECIMAL(10, 2), Order_status ENUM('待发货', '已发货', '待确认','已完成') ); 以上代码如何创建触发器，使得product_supplier表中某个商品的供货商发生改变时库存表中的对应商品的供货商也同步更新

可以使用以下触发器实现该功能：

CREATE TRIGGER `update_supplier` AFTER UPDATE ON `Product_Supplier`
FOR EACH ROW
BEGIN
	UPDATE Stock SET Supplier_id = NEW.Supplier_id WHERE Product_id = NEW.Product_id;
END;

这个触发器会在Product_Supplier表中某个商品的供货商发生改变时触发，然后将库存表Stock中对应商品的供货商更新为新的供货商。