MySQL 触发器

 

创建触发器

在创建触发器时，需要给出 4条信息：

        1、唯一的触发器名；

        2、触发器关联的表

        3、触发器应该响应的动作（ DELETE、INSERT 或UPDATE）

        4、触发器何时执行（处理之前或之后）。

 

  注意：只有表才支持触发器，视图不支持（临时表也不支持）。

    触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6个触发器（每条INSERT、 UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对 INSERT和UPDATE 操作执行的触发器，则应该定义两个触发器。

  注意：如果BEFORE触发器失败，则 MySQL将不执行请求的操作。此外，如果 BEFORE触发器或语句本身失败， MySQL将不执行 AFTER触发器（如果有的话）。

 

　触发器 可以 使用BEGIN 和END 语句标记触发器体。但不是必需的。使用 BEGIN END 块的好处是触发器能容纳多条语句。

 

INSERT触发器

 

    INSERT触发器在INSERT 语句执行之前或之后执行。需要知道以下几点：

 

        1、在INSERT触发器代码内，可引用一个名为 NEW的虚拟表，访问被插入的行。

 

        2、在BEFORE INSERT触发器中， NEW中的值也可以被更新（允许更改被插入的值）；

 

        3、对于 AUTO_INCREMENT列，NEW 在INSERT执行之前包含 0，在INSERT执行之后包含新的自动生成值。

 

 

drop trigger if exists test_trigger;  ##删除触发器
delimiter//  ##改变语句分割符
create trigger test_trigger after insert on aaa_test　　　　##创建触发器test_trigger，当表aaa_test插入数据后执行
for each row　　　　##FOR EACH ROW是触发器的执行间隔，FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次
begin
    select new.id into @aaa;　　　　##将aaa_test表中id列的最新的值保存到用户变量@aaa中
end//

往表aaa_test中插入一行数据：

INSERT INTO aaa_test(username,PASSWORD,birthday) VALUES('test_trigger','12345',CURRENT_DATE);

此时表aaa_test中的结果：

 

查看标量@aaa的结果，执行 ：  "SELECT @aaa",其结果等于最新id值

 

 

DELETE触发器

    DELETE 触发器在DELETE 语句执行之前或之后执行。需要知道以下两点：

        1、在 DELETE触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；

        2、OLD 中的值全都是只读的，不能更新。

 

DROP TRIGGER IF EXISTS test_delete_trigger_before;
DELIMITER//
CREATE TRIGGER test_delete_trigger_before BEFORE DELETE ON aaa_test
FOR EACH ROW
BEGIN
    SELECT old.id INTO @bbb;   ##将要删除数据行的id值保存到变量@bbb中
END//

删除之前aaa_test表中数据为：

删除id=48的记录，执行：

DELETE FROM aaa_test WHERE id=48;

查看变量@bbb，执行：

SELECT @bbb;

 其结果为48：

 

UPDATE触发器

    UPDATE 触发器在UPDATE 语句执行之前或之后执行。需要知道以下几点：

        1、在 UPDATE触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；

        2、在 BEFORE UPDATE触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；

        3、OLD 中的值全都是只读的，不能更新。

 

DROP TRIGGER IF EXISTS test_update_trigger_before;
DELIMITER//
CREATE TRIGGER test_update_trigger_before BEFORE UPDATE ON aaa_test
FOR EACH ROW
BEGIN
    SELECT UPPER(old.password) INTO @ccc;　　##将修改之前的值保存到@ccc变量中
    SELECT LOWER(new.password ) INTO @ddd;  ##将修改后的新值保存到@ddd变量中
END//

修改密码之前：

修改密码：

update  aaa_test set password='helloWorld' where id=49;

变量@ccc结果为修改之前的值：

SELECT @ccc;

 

变量@ddd结果为修改后的值：

SELECT @ddd;

 

 

    【注】： MySQL 触发器中不支持 CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

 

    关于什么时候使用new，old这两个关键字就好像字面意思新旧一样：

         INSERT 只有NEW
         UPDATE既有NEW又和OLD
         DELETE只有OLD 

    所以对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及 OLD同时使用