MySQL之触发器详解

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为 BEFORE 或者 AFTER
trigger_event：触发事件，为 INSERT、DELETE 或者 UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条 SQL 语句或者是用 BEGIN 和 END 包含的多条语句
所以可以说 MySQL 创建以下六种触发器：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

DELIMITER $  #将语句的分隔符改为 $
BEGIN
sql1;
sql2;
...
sqln
END $
DELIMITER ;  #将语句的分隔符改回原来的分号 ";"

DECLARE var_name var_type [DEFAULT value] #定义变量，可指定默认值
SET var_name = value  #给变量赋值 

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据 

mysql> desc userinfo;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | YES  |     | NULL    |                |
| passwd   | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> desc log;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| log   | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON userinfo FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40);
DECLARE s2 VARCHAR(20);
SET s2 = "is created";
SET s1 = CONCAT(NEW.username,s2);     #函数 CONCAT 可以将字符串连接
INSERT INTO log(log) values(s1);
END $
DELIMITER ;

mysql> insert into userinfo(username,passwd) values('frank','123');
Query OK, 1 row affected (0.01 sec)
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
|  1 | frank    | 123    |
+----+----------+--------+
1 row in set (0.00 sec)

mysql> select * from log;
+----+------------------+
| id | log              |
+----+------------------+
|  1 | frank is created |
+----+------------------+
1 row in set (0.00 sec)

DROP TRIGGER [db_name.]trigger_name   #如果不指定 db_name，默认为当前的数据库。

mysql> drop trigger user_log;
Query OK, 0 rows affected (0.00 sec)

mysql> show triggers  \G;
*************************** 1. row ***************************
             Trigger: user_log
               Event: INSERT
               Table: userinfo
           Statement: BEGIN
DECLARE s1 VARCHAR(40);
DECLARE s2 VARCHAR(20);
SET s2 = "is created";
SET s1 = CONCAT(NEW.username,s2);
INSERT INTO log(log) values(s1);
END
              Timing: AFTER
             Created: 2017-09-22 21:12:46.02
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
 
ERROR:
No query specified