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 ;
在userinfo中插入数据并查看数据：
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)
好的，我们再来查看一下log表吧！
mysql> select * from log; +----+------------------+ | id | log | +----+------------------+ | 1 | frank is created | +----+------------------+ 1 row in set (0.00 sec)
通过上面的例子，可以看到只需要在userinfo中插入用户的信息，日志会自动记录到log表中，这也许就是触发器给我带来的便捷吧！
删除触发器一次可以删除一个触发器，语法如下：
DROP TRIGGER [db_name.]trigger_name #如果不指定db_name，默认为当前的数据库。
比如删除上面例子的触发器：
mysql> drop trigger user_log; Query OK, 0 rows affected (0.00 sec)
查看触发器可以通过show triggers命令查看触发器的状态：
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
限制和注意事项触发器会有以下两种限制：