MySQL 触发器

触发器overview

触发器 (Trigger) is and 表关联 特殊store过程, 当表 on 发生specificevent (such asINSERT, UPDATE, DELETEoperation) 时, 会自动执行 SQL语句collection.

触发器 特点

• 自动执行: 当触发event发生时, 触发器会自动执行, 无需手动调用
• and 表关联: 触发器总 is and specific 表相关联
• event驱动: 由specific datalibraryevent触发, such asINSERT, UPDATE, DELETE
• transaction性: 触发器 执行 is transaction 一部分, 可以rollback
• 隐式执行: 触发器 in after 台隐式执行, for user透明

触发器 application场景

• dataverification: in data插入 or update before verificationdata has 效性
• dataintegrity: maintenance表之间 参照integrity
• audit跟踪: 记录data 变更history
• 自动计算: 自动计算fork值 or 汇总data
• synchronizationupdate: 当一个表 data变更时, 自动update相关表 data
• complex 业务规则: implementation complex 业务逻辑规则

触发器 creation

in MySQLin, usingCREATE TRIGGER语句creation触发器.

basic语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器code
END;

语法说明

• trigger_name: 触发器 名称
• BEFORE | AFTER: 指定触发器 in event发生 before 还 is 发生 after 执行
• INSERT | UPDATE | DELETE: 指定触发event class型
• table_name: 触发器关联 表名
• FOR EACH ROW: 表示 for 每一行data都执行触发器

example: creationINSERT触发器

DELIMITER //
CREATE TRIGGER before_customer_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

example: creationUPDATE触发器

DELIMITER //
CREATE TRIGGER before_customer_update
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

example: creationDELETE触发器

DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_history(order_id, customer_id, delete_date)
    VALUES(OLD.id, OLD.customer_id, NOW());
END //
DELIMITER ;

触发器in 特殊variable

MySQL触发器inproviding了两个特殊variable, 用于访问触发 before after data:

NEW and OLDvariable

usingexample

-- verificationupdateoperation
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '价格不能 for 负数';
    END IF;
    
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'library存不能 for 负数';
    END IF;
END //
DELIMITER ;

触发器 class型

根据触发时机 and eventclass型 不同, MySQL触发器可以分 for 以 under 几种class型:

按触发时机classification

• BEFORE触发器: in event发生 before 执行, 可以用于verificationdata or modify将要插入/update data
• AFTER触发器: in event发生 after 执行, 可以用于记录log, update关联表etc.operation

按eventclass型classification

• INSERT触发器: in 插入data时触发
• UPDATE触发器: in updatedata时触发
• DELETE触发器: in deletedata时触发

组合class型

触发器 management

查看触发器

-- 查看所 has 触发器
SHOW TRIGGERS;

-- 查看specific表 触发器
SHOW TRIGGERS LIKE 'table_name%';

--  from information_schemainquery触发器information
SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'database_name' 
AND EVENT_OBJECT_TABLE = 'table_name';

delete触发器

DROP TRIGGER IF EXISTS trigger_name;

modify触发器

MySQL不support直接modify触发器, 需要先delete再重 new creation:

-- delete old 触发器
DROP TRIGGER IF EXISTS trigger_name;

-- creation new 触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    --  new  触发器code
END;

触发器 usingtechniques

dataverificationexample

-- verification订单数量
DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.quantity <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '订单数量必须 big 于0';
    END IF;
    
    IF NEW.total_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '订单金额必须 big 于0';
    END IF;
END //
DELIMITER ;

audit跟踪example

-- 记录产品价格变更
DELIMITER //
CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history(product_id, old_price, new_price, change_date)
        VALUES(OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END //
DELIMITER ;

级联updateexample

-- 当产品价格变更时, update订单详情in 价格
DELIMITER //
CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        UPDATE order_details 
        SET unit_price = NEW.price 
        WHERE product_id = OLD.id 
        AND order_id IN (SELECT id FROM orders WHERE status = 'pending');
    END IF;
END //
DELIMITER ;

触发器 限制 and Notes

触发器 限制

• 不能using某些语句: 触发器in不能usingSTART TRANSACTION, COMMIT, ROLLBACKetc.transaction控制语句
• performance影响: complex 触发器可能会影响datalibraryperformance, 特别 is in big 量dataoperation时
• 递归调用: 触发器不能递归调用, 可能会导致无限循环
• debug difficult : 触发器 执行 is 隐式 , debug比较 difficult
• portability差: 不同datalibrarysystem 触发器语法 and functions has 所不同

Notes

触发器 and store过程 区别

触发器best practices

命名规范

• using has 意义 名称, such asbefore_customer_insert
• package含触发时机 (before/after) , 表名 and eventclass型 (insert/update/delete)
• using under 划线分隔单词, improving readable 性

performanceoptimization

• 保持触发器code简洁, 避免 complex 逻辑
• 避免 in 触发器in执行 big 量 datalibraryoperation
• 只 in 必要时using触发器, 考虑other替代solutions
• for 于频繁update 表, 谨慎using触发器

security性

• 确保触发器 逻辑正确, 避免data损 bad
• for 触发器for充分 test, 确保 in 各种circumstances under 都能正常工作
• 限制触发器 permission, 只授予必要 permission

可maintenance性

• for 触发器添加详细 comment, 说明其用途 and 逻辑
• documentation化所 has 触发器, including触发条件, 逻辑 and 影响
• 定期审查 and optimization触发器, 确保其仍然符合业务requirements
• 考虑usingversion控制management触发器code

实践case

-- creation订单插入触发器
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON order_details
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

-- creation订单delete触发器
DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON order_details
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock + OLD.quantity 
    WHERE id = OLD.product_id;
END //
DELIMITER ;

-- creation订单update触发器
DELIMITER //
CREATE TRIGGER after_order_update
AFTER UPDATE ON order_details
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock + OLD.quantity - NEW.quantity 
    WHERE id = OLD.product_id;
END //
DELIMITER ;

-- creationuserloginaudit触发器
DELIMITER //
CREATE TRIGGER after_user_login
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.last_login != NEW.last_login THEN
        INSERT INTO user_audit(user_id, action, action_date, ip_address)
        VALUES(OLD.id, 'login', NEW.last_login, NEW.last_ip);
    END IF;
END //
DELIMITER ;

-- creationuserinformation变更audit触发器
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO user_audit(user_id, action, action_date, details)
        VALUES(OLD.id, 'email_change', NOW(), 
               CONCAT(' from  ', OLD.email, ' 改 for  ', NEW.email));
    END IF;
    
    IF OLD.password != NEW.password THEN
        INSERT INTO user_audit(user_id, action, action_date, details)
        VALUES(OLD.id, 'password_change', NOW(), 'password已更改');
    END IF;
END //
DELIMITER ;

互动练习

DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- verification邮箱格式
    IF NEW.email NOT REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;
    
    -- verificationpassword long 度
    IF LENGTH(NEW.password) < 6 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'password long 度至 few  for 6位';
    END IF;
    
    -- 设置creation时间 and update时间
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

-- 首先creation客户history表
CREATE TABLE customer_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    delete_date DATETIME,
    INDEX idx_customer_id (customer_id)
);

-- creationdelete触发器
DELIMITER //
CREATE TRIGGER after_customer_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    INSERT INTO customer_history(customer_id, name, email, phone, delete_date)
    VALUES(OLD.id, OLD.name, OLD.email, OLD.phone, NOW());
END //
DELIMITER ;