MySQL 插入更新触发器脚本：记录订单历史 - 详细教程

-- 创建一个表用于触发器\nCREATE TABLE orders (\n id INT PRIMARY KEY AUTO_INCREMENT,\n order_number VARCHAR(10),\n total_amount DECIMAL(10, 2)\n);\n\n-- 创建一个表用于存储订单历史记录\nCREATE TABLE order_history (\n id INT PRIMARY KEY AUTO_INCREMENT,\n order_id INT,\n order_number VARCHAR(10),\n total_amount DECIMAL(10, 2),\n action VARCHAR(10),\n action_date TIMESTAMP\n);\n\n-- 创建插入触发器，每次插入新订单时将订单数据插入order_history表\nDELIMITER $$\nCREATE TRIGGER insert_order_trigger AFTER INSERT ON orders\nFOR EACH ROW\nBEGIN\n INSERT INTO order_history (order_id, order_number, total_amount, action, action_date)\n VALUES (NEW.id, NEW.order_number, NEW.total_amount, 'INSERT', NOW());\nEND$$\nDELIMITER ;\n\n-- 创建更新触发器，每次更新订单时将订单数据插入order_history表\nDELIMITER $$\nCREATE TRIGGER update_order_trigger AFTER UPDATE ON orders\nFOR EACH ROW\nBEGIN\n IF NEW.order_number <> OLD.order_number OR NEW.total_amount <> OLD.total_amount THEN\n INSERT INTO order_history (order_id, order_number, total_amount, action, action_date)\n VALUES (NEW.id, NEW.order_number, NEW.total_amount, 'UPDATE', NOW());\n END IF;\nEND$$\nDELIMITER ;\n\n-- 测试触发器\nINSERT INTO orders (order_number, total_amount) VALUES ('1001', 50.00);\nUPDATE orders SET total_amount = 75.00 WHERE id = 1;\n\n-- 检查order_history表中的数据\nSELECT * FROM order_history;