MySQL基础5（触发器）

触发器简介：

• SQL触发器是存储在数据库目录中的一组SQL语句。每当与表相关联的事件发生时，即会执行或触发SQL触发器，例如插入，更新或删除。
• SQL触发器是一种特殊类型的存储过程。 这是特别的，因为它不像直接像存储过程那样调用。 触发器和存储过程之间的主要区别在于，当对表执行数据修改事件时，会自动调用触发器，而存储过程必须要明确地调用。
• 在MySQL5.7.2版本之前，每个表最多可以定义六个触发器。从MySQL 5.7.2+版本开始，可以为相同的触发事件和动作时间定义多个触发器。从MySQL 5.1.4版本开始，触发器可以调用存储过程或存储函数。

MySQL触发器关键词解释：

• BEFORE INSERT - 在数据插入表之前被激活触发器。
• AFTER INSERT - 在将数据插入表之后激活触发器。
• BEFORE UPDATE - 在表中的数据更新之前激活触发器。
• AFTER UPDATE - 在表中的数据更新之后激活触发器。
• BEFORE DELETE - 在从表中删除数据之前激活触发器。
• AFTER DELETE - 从表中删除数据之后激活触发器。

MySQL触发器创建语法

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END;

我们来更详细的检查上面的语法。

• 将触发器名称放在CREATE TRIGGER语句之后。触发器名称应遵循命名约定

[trigger time]_[table name]_[trigger event]，例如before_employees_update。

• 触发激活时间可以在之前或之后。必须指定定义触发器的激活时间。如果要在更改之前处理操作，则使用BEFORE关键字，如果在更改后需要处理操作，则使用AFTER关键字。
• 触发事件可以是INSERT，UPDATE或DELETE。此事件导致触发器被调用。 触发器只能由一个事件调用。要定义由多个事件调用的触发器，必须定义多个触发器，每个事件一个触发器。
• 触发器必须与特定表关联。没有表触发器将不存在，所以必须在ON关键字之后指定表名。
• 将SQL语句放在BEGIN和END块之间。这是定义触发器逻辑的位置。

实例：

1.首先创建名为 fruit 的数据库，然后在 fruit 的数据库中创建fruit_price表和fruitchange_log表。

2.1创建fruit_price表

USE fruit;
CREATE TABLE fruit_price (
    fruit_id  INT (10) , 
    fruit_name VARCHAR(50) NOT NULL,
    fruit_price DECIMALNOT NULL
 );

2.2 插入数据：

INSERT INTO  fruit.fruit_price ( fruit_id, fruit_name,fruit_price )
                       VALUES
                       ( 1, 'pear',3 );
                       ( 2, 'peach',4)
                       ( 3, 'banana',5)

3.创建fruitchange_log表：

USE food;
CREATE TABLE fruitchange_log (
    fruit_name VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    change_date DATETIME  NOT NULL
);

4.创建触发器，当更改fruit_price表时，先备份要修改的fruit_price的信息到fruitchange_log表中。

DELIMITER $$
CREATE TRIGGER before_fruit_price_update 
    BEFORE UPDATE ON fruit_price
    FOR EACH ROW 
BEGIN
    INSERT INTO fruitchange_log
    SET action = 'UPDATE',
     fruit_name  = OLD.fruit_name ,
        fruit_price = OLD.fruit_price,
        changedate = NOW(); 
END$$
DELIMITER ;

说明：
1.在触发器的主体中，使用OLD关键字来访问受触发器影响的行的fruit_name和fruit_price列。
2.在为INSERT定义的触发器中，仅能使用NEW关键字。不能使用OLD关键字。但是，在为DELETE定义的触发器中，没有新行，因此只能使用OLD关键字。在UPDATE触发器中，OLD是指更新前的行，而NEW是更新后的行。

5. 查看触发器。语句如下：

SHOW TRIGGERS;

6. 修改fruit_price表的 数据，查看修改是否生效。

UPDATE fruit_price 
SET 
fruit_name = 'apple'
fruit_price = 10;
WHERE
    fruit_id = 1;

7. 查看fruitchange_log表的 数据，来验证触发器是否生效。

看图可知，fruitchange_log表中多了一行数据，正是修改之前的fruit_ptice表的数据，这说明创建的触发器有效！

MySQL创建多个触发器

待更新……

MySQL触发器管理

待更新……

MySQL触发限制

MySQL触发器覆盖标准SQL中定义的所有功能。 但是，在应用程序中使用它们之前也有一些限制。

• MySQL触发器不能使用在SHOW，LOAD DATA，LOAD TABLE，BACKUP DATABASE，RESTORE，FLUSH和RETURN语句之上。
• MySQL触发器不能使用隐式或明确提交或回滚的语句，如COMMIT，ROLLBACK，START TRANSACTION，LOCK/UNLOCK TABLES，ALTER，CREATE，DROP，RENAME等。
• MySQL触发器不能使用准备语句，如PREPARE，EXECUTE等
• MySQL触发器不能使用动态SQL语句。