触发器学习笔记

触发器学习

概念：触发器是一种特殊的存储过程，MySQL5.0以上版本支持触发器，触发器是与表有关的命名数据库对象，当表出现特定事件时，将激活该对象。
触发器可以处理很多需要程序来完成的工作，这样做的好处是减轻程序带来的各种各样的问题如，效率、bug和维护等。
当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，就会自动执行触发器所定义的SQL 语句。

权限说明:
激活触发程序时，对于触发程序引用的所有OLD和NEW列，需要具有SELECT权限，对于作为SET赋值目标的所有NEW列，需要具有UPDATE权限。
CREATE TRIGGER语句需要SUPER权限。


注意事项：
1、tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。
2、对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
3、在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列。
4、trigger_event能被LOAD DATE语句激活；
5、触发程序不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK；
6、用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值；
7、在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，不是实际插入新记录时将自动生成的序列号。
8、触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL；


创建触发器
创建触发器的语法：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
trigger_name 触发器的名称。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·         INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
·         UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
·         DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。

简单的例子：
统计表content中记录的条数，插入的时候需要通过促发起增加count_content字段的值，删除的时候需要减小count_content字段的值
DELIMITER $$
CREATE TRIGGER `tr_count_insert` AFTER INSERT on `content`
FOR EACH ROW BEGIN
  update t_count set count_content = count_content + 1;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER `tr_count_delete` AFTER DELETE on `content`
FOR EACH ROW BEGIN
  update t_count set count_content = count_content - 1;
END$$
DELIMITER ;


将mount的值限定在0-100之间，如果小于0的话会更改成0，如果大于100的话会更改成100
create table test (account int(10),mount decimal(10,2))engine=innodb;

delimiter //
create trigger test_check before insert on test
for each row
begin
    if new.mount < 0 then
        set new.mount=0;
elseif new.mount > 100 then
     set new.mount = 100;
end if;
end;//
delimiter ;

insert into test values(10,-100),(12,120);

select * from test;
+---------+--------+
| account | mount  |
+---------+--------+
|      10 |   0.00 |
|      12 | 100.00 |
+---------+--------+


查询触发器
命令：SHOW TRIGGERS;


删除触发器
语法：
DROP TRIGGER [schema_name.]trigger_name
该语句用户删除触发器，[schema_name.]用于指定所在的数据库的名称，若未加，则默认为当前选中的数据库。
使用举例：删除前面建的触发器：
drop trigger test_check;