— 更新触发器
DELIMITER //
CREATE OR REPLACE TRIGGER fgedu_before_insert_user
BEFORE INSERT ON fgedu_users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END //
DELIMITER ;

— 更新事件
DELIMITER //
ALTER EVENT fgedu_cleanup_logs
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
DELETE FROM fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY); END // DELIMITER ; -- 监控触发器与事件的执行情况 -- 启用慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/mysql/data/fgedu-slow.log'; SET GLOBAL long_query_time = 1;

— 创建BEFORE INSERT触发器
DELIMITER //
CREATE TRIGGER fgedu_before_insert_user
BEFORE INSERT ON fgedu_users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
END //
DELIMITER ;

— 创建AFTER UPDATE触发器
DELIMITER //
CREATE TRIGGER fgedu_after_update_user
AFTER UPDATE ON fgedu_users
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
END //
DELIMITER ;

— 创建BEFORE DELETE触发器
DELIMITER //
CREATE TRIGGER fgedu_before_delete_user
BEFORE DELETE ON fgedu_users
FOR EACH ROW
BEGIN
INSERT INTO fgedu_user_logs (user_id, action, created_at)
VALUES (OLD.id, ‘DELETE’, NOW());
END //
DELIMITER ;

— 创建事件：清理日志
DELIMITER //
CREATE EVENT fgedu_cleanup_logs
ON SCHEDULE EVERY 1 DAY
STARTS ‘2024-01-01 00:00:00’
DO
BEGIN
DELETE FROM fgedu_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY); END // DELIMITER ; -- 创建事件：更新统计数据 DELIMITER // CREATE EVENT fgedu_update_stats ON SCHEDULE EVERY 1 HOUR DO BEGIN UPDATE fgedu_stats SET total_users = (SELECT COUNT(*) FROM fgedu_users); UPDATE fgedu_stats SET total_orders = (SELECT COUNT(*) FROM fgedu_orders); END // DELIMITER ; -- 查看事件状态 SHOW EVENTS WHERE db = 'fgedudb';

— 原触发器（未优化）
DELIMITER //
CREATE TRIGGER fgedu_before_insert_order
BEFORE INSERT ON fgedu_orders
FOR EACH ROW
BEGIN
— 检查库存
DECLARE stock INT;
SELECT stock INTO stock FROM fgedu_products WHERE id = NEW.product_id;
IF stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; -- 更新库存 UPDATE fgedu_products SET stock = stock - NEW.quantity WHERE id = NEW.product_id; -- 计算总价 DECLARE price DECIMAL(10,2); SELECT price INTO price FROM fgedu_products WHERE id = NEW.product_id; SET NEW.amount = price * NEW.quantity; END // DELIMITER ; -- 优化后触发器 DELIMITER // CREATE TRIGGER fgedu_before_insert_order BEFORE INSERT ON fgedu_orders FOR EACH ROW BEGIN -- 检查库存并计算总价 DECLARE stock INT; DECLARE price DECIMAL(10,2); SELECT stock, price INTO stock, price FROM fgedu_products WHERE id = NEW.product_id; IF stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; -- 更新库存 UPDATE fgedu_products SET stock = stock - NEW.quantity WHERE id = NEW.product_id; -- 计算总价 SET NEW.amount = price * NEW.quantity; END // DELIMITER ;

— 创建触发器：自动记录操作日志
DELIMITER //
CREATE TRIGGER fgedu_after_update_product
AFTER UPDATE ON fgedu_products
FOR EACH ROW
BEGIN
INSERT INTO fgedu_product_logs (
product_id,
old_name,
new_name,
old_price,
new_price,
old_stock,
new_stock,
action,
created_at
) VALUES (
OLD.id,
OLD.name,
NEW.name,
OLD.price,
NEW.price,
OLD.stock,
NEW.stock,
‘UPDATE’,
NOW()
);
END //
DELIMITER ;

— 测试触发器
UPDATE fgedu_products SET price = 150.00 WHERE id = 1;
SELECT * FROM fgedu_product_logs;

— 创建事件：备份数据
DELIMITER //
CREATE EVENT fgedu_backup_data
ON SCHEDULE EVERY 1 WEEK
STARTS ‘2024-01-01 00:00:00’
DO
BEGIN
— 备份用户表
CREATE TABLE fgedu_users_backup AS SELECT * FROM fgedu_users;

— 备份订单表
CREATE TABLE fgedu_orders_backup AS SELECT * FROM fgedu_orders;

— 记录备份日志
INSERT INTO fgedu_backup_logs (backup_time, status)
VALUES (NOW(), ‘SUCCESS’);
END //
DELIMITER ;

— 查看事件
SHOW EVENTS WHERE db = ‘fgedudb’;

— 优化前：触发器执行多个查询
DELIMITER //
CREATE TRIGGER fgedu_before_insert_order
BEFORE INSERT ON fgedu_orders
FOR EACH ROW
BEGIN
— 检查用户是否存在
DECLARE user_exists INT;
SELECT COUNT(*) INTO user_exists FROM fgedu_users WHERE id = NEW.customer_id;
IF user_exists = 0 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘User not found’;
END IF;

— 检查产品是否存在
DECLARE product_exists INT;
SELECT COUNT(*) INTO product_exists FROM fgedu_products WHERE id = NEW.product_id;
IF product_exists = 0 THEN
SIGNAL SQLSTATE ‘45000’ SET MESSAGE_TEXT = ‘Product not found’;
END IF;

— 检查库存
DECLARE stock INT;
SELECT stock INTO stock FROM fgedu_products WHERE id = NEW.product_id;
IF stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; END // DELIMITER ; -- 优化后：触发器执行单次查询 DELIMITER // CREATE TRIGGER fgedu_before_insert_order BEFORE INSERT ON fgedu_orders FOR EACH ROW BEGIN -- 检查用户和产品 DECLARE user_exists INT; DECLARE product_exists INT; DECLARE stock INT; SELECT (SELECT COUNT(*) FROM fgedu_users WHERE id = NEW.customer_id), (SELECT COUNT(*) FROM fgedu_products WHERE id = NEW.product_id), (SELECT stock FROM fgedu_products WHERE id = NEW.product_id) INTO user_exists, product_exists, stock; IF user_exists = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'User not found'; END IF; IF product_exists = 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found'; END IF; IF stock < NEW.quantity THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock'; END IF; END // DELIMITER ;