六、触发器

一、触发器-TRIGGER

触发器是MySQL响应INSERT、UPDATA、DELETE任意语句而自动执行的一条MySQL语句，因此，当需要某条语句在执行时候，自动运行某些动作，可以使用触发器。触发器大体来讲可以分为”前置”跟”后发”两种，在创建时候通过BEFORE、AFTER关键字确定。

二、创建

• 触发器创建模型

DELIMITER //
CREATE TRIGGER triggerName
    AFTER/BEFORE INSERT/UPDATE/DELETE ON tableName
    FOR EACH ROW
    BEGIN
        ...
    END //
DELIMITER ;

另外值得一提的是，在触发器中可以通过@@IDENTITY或者LAST_INSERT_ID()来获取最后插入的自增ID

• 代码示例(假设tableA有自增长id)

DELIMITER //
CREATE TRIGGER auto_trigger
    AFTER INSERT ON tableA
    FOR EACH ROW
    BEGIN
        SET @auto_id = @@IDENTITY;
    END //
DELIMITER ;

INSERT INTO tableA;
SELECT FROM @auto_id;

注意，试了一下，这里@@IDENTITY的值是从0开始的，比真正插入的自增长ID少1

三、查看

• 查看所有库中的所有触发器

SELECT * FROM information_schema.`TRIGGERS`;

• 查看当前库中的所有触发器

SHOW TRIGGERS;

• 查看指定触发器

SHOW TRIGGERS FROM dataBaseName like "triggerName%";

四、删除

DROP TRIGGER triggerName;

五、细节

根据响应语句的不同，触发器还可以分成三类触发器，即INSERT触发器、UPDATE触发器、DELETE触发器

1.INSERT触发器

• 在INSERT触发器中可引用一个名为NEW的虚拟表，存放了插入的数据，因此可以通过NEW来访问插入数据
• 在BEFORE INSERT触发器内，NEW中的值是可以被修改的，也就是说可以在触发器中修改将要插入的数据
• 对于自增长AUTO_INCREMENT对应的列，NEW在INSERT执行之前为0，执行之后包含最新插入的自增长值

DELIMITER //
CREATE TRIGGER before_insert_trigger
    BEFORE INSERT ON tableA
    FOR EACH ROW
    BEGIN
        IF NEW.id = 3 THEN
        SET NEW.id = 88;
        END IF;
    END //
DELIMITER ;

2.DELETE触发器

• 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，存放了删除的数据，因此可以通过OLD来访问删除数据
• OLD中的值都是只读的，不能更新

DELIMITER //
CREATE TRIGGER after_delete_trigger
    AFTER DELETE ON testA
    FOR EACH ROW
    BEGIN
        INSERT INTO testB VALUES(OLD.id, OLD.name);
    END //
DELIMITER ;

3.UPDATE触发器

• 在UPDATE触发器代码内，可以OLD虚拟表访旧值和NEW虚拟表访问新更新的值
• 在BEFORE UPDATE触发器中，NEW中的值也可以做修改
• OLD中的值全都是只读的

DELIMITER //
CREATE TRIGGER before_update_trigger
    BEFORE UPDATE ON tableA
    FOR EACH ROW
    BEGIN
        IF NEW.name = 'change' THEN
        SET NEW.name = 'changed';
        END IF;
        SET @old_name = OLD.name;
    END //
DELIMITER ;

INSERT INTO tableA(name) values('ok');
UPDATE tableA SET name = 'change' WHERE id = 1;
SELECT @old_name;