菜鷄日記——数据库触发器

触发器（trigger）：当对数据库修改，并满足一定条件时，自动被DBMS执行的SQL语句

• 有insert、delete、update触发器，按数据更新前后可分成before、after触发器
• 创建触发器的权限默认分配给表的所有者，且不能转移（即只有表的所有者能创建）
• 只能在当前数据库中创建，但是可以引用其他数据库中的对象
• 若表的外键已经定义了级联的delete或update，则不能定义before的delete或update触发器
• truncate table（删除表中所有元组）不会触发delete触发器

SQL标准定义的触发器语法：

create trigger 触发器名 before(或after) insert(或delete) on 表/视图

        referencing new(或old) row as 过渡变量（方便下面引用，指向的是被插入或被删除的元组）

        for each row

        when 条件语句

        begin

                执行语句列表（每句以分号结尾）

        end;

---

create trigger 触发器名 before(或after) update of 表/视图 on (属性1，属性2，……，属性n)

        referencing old row as 过渡变量1（指向的是该元组修改前）

        referencing new row as 过渡变量2（指向的是该元组修改后）

        ……

---

上面创建的触发器针对的是存在数据更新的元组，下面的则是针对存在数据更新的元组组成的表（过渡表）

……

        referencing old(或new) table as 过渡表名

        for each statement

        ……

需要注意的是过渡表不能用在before触发器

---

alter trigger 触发器名 disable(或enable);    触发器的禁用和启用

drop trigger 触发器名;

MySQL8.0的触发器语法：

• 具体情况查阅MySQL用户手册https://dev.mysql.com/doc/refman/8.0/en/triggers.html
• 因为执行语句列表的分隔符为分号，与SQL语句的分隔符相同，所以需要先重定义SQL的分隔符，避免混淆
• 不支持上述的referencing……as……语句，也不支持for each statement

delimiter $
create trigger trigr  before insert on tbl
        for each row
        begin
                if tbl.var < 0 then
                        update tbl set var=0;
                endif;
                if new.var > 10 then
                       set new.var=10;
                endif;
        end$    （$表示一条SQL语句的结束，和重定义分隔符前的;一样）
delimiter ;

drop trigger if exists trigr;

上面的new指示的是更新的元组，在MySQL8.0中，只有before insert和before update能用new，同时没有old。还有重要一点：MySQL触发器中不允许干涉事务，也就是说无法使用rollback等事物语句。