mysql触发器

浏览数：25 / 时间：2015年06月12日

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发程序格式：

CREATE TRIGGER [USER NAME | CURRENT_USER] 触发器名字触发时刻触发事件 ON 表名 FOR EACH ROW 动作

触发时刻是触发程序的动作时间。它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。

触发事件指明了激活触发程序的语句的类型。可以是下述值之一：

INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

使用别名OLD和NEW，能够引用与触发程序相关的表中的列。OLD.col_name在更新或删除它之前，引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。

例：当插入的学生成绩小于等于60时，将成绩改为70.

mysql> create trigger tri before insert on student for each row begin 
    -> if new.score <= 60 
    -> then set new.score=70; 
    -> end if; end//
Query OK, 0 rows affected (0.09 sec)
mysql> insert into student value(‘ch‘,‘china‘,2,60)//
mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
| ch   | china   |    2 |    70 |
+------+---------+------+-------+
2 rows in set (0.00 sec)

事件和触发器相似，当在数据库上启动一条语句的时候，触发器就启动了，而事件是据调度事件来启动的，也成为临时性触发器，因为一个事件只能调用一次。一个事件也可以周期性的执行，例如每周二凌晨0点。

事件格式：

create event 事件名 on schedule <调度时间表达式> do 动作

一个事件可以是打开的或者关闭的，打开意味着调度器检查事件动作是否必须调用，关闭意味着事件的声明存储在目录中，但是调度器不会检查它是否应该调用。

注：可能数据库没有打开调度器，可以通过set GLOBAL EVENT_SCHEDULER=true打开
例：创建一个事件删除表中sid=2的学生信息
原始表信息：

mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
| ch   | china   |    2 |    70 |
+------+---------+------+-------+
2 rows in set (0.01 sec)

创建事件：创建后立即执行(on schedule at now())

mysql> create event eve on schedule at now() 
    -> do delete from student where sid=2//
Query OK, 0 rows affected (0.00 sec)


mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
+------+---------+------+-------+
1 row in set (0.00 sec)

创建周期性事件，每秒插入一条数据，持续两秒

mysql> create event eve on schedule every 1 second 
    -> starts now() ends now()+interval 1 second 
    -> do insert into student(name) values(‘sh‘)//


mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
| sh   | NULL    | NULL |  NULL |
| sh   | NULL    | NULL |  NULL |
+------+---------+------+-------+
3 rows in set (0.02 sec)

如果没有ends语句的话，这个事件会一直调度，直到这个事件被删除。

调度时间也可以是一个case表达式。

例：创建事件，如果今天是周三时就插入语句

mysql> create event eve on schedule at 
    -> case dayname(now()) when ‘wednesday‘ then now() end 
    -> do insert into student(name) values(‘sh‘)//
mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
| sh   | NULL    | NULL |  NULL |
+------+---------+------+-------+
2 rows in set (0.00 sec)