当前位置:Gxlcms > 数据库问题 > MySQL之触发器

MySQL之触发器

时间:2021-07-01 10:21:17 帮助过:5人阅读

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

tigger_event:

 技术分享

load data语句是将文件的内容插入到表中,相当于是insert语句,而replace语句在一般的情况下和insert差不多,但是如果表中存在primary 或者unique索引的时候,如果插入的数据和原来的primary key或者unique相同的时候,会删除原来的数据,然后增加一条新的数据,所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

 trigger_stmt:

trigger_stmt可以是一条SQL语句,也可以是多条SQL代码块,那如何创建呢?

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

在BEGIN...END语句中也可以定义变量,但是只能在BEGIN...END内部使用:

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

NEW和OLD的使用:

 技术分享

根据以上的表格,可以使用一下格式来使用相应的数据:

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)

需求是:当在userinfo中插入一条数据,就会在log中生成一条日志信息。

创建触发器:

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

 

限制和注意事项

触发器会有以下两种限制:

1.触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。

2.不能再触发器中使用以显示或隐式方式开始或结束事务的语句,如START TRANS-ACTION,COMMIT或ROLLBACK。

注意事项:MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果是对非事务表进行操作,那么就无法回滚了,数据可能会出错。

 

总结

触发器是基于行触发的,所以删除、新增或者修改操作可能都会激活触发器,所以不要编写过于复杂的触发器,也不要增加过得的触发器,这样会对数据的插入、修改或者删除带来比较严重的影响,同时也会带来可移植性差的后果,所以在设计触发器的时候一定要有所考虑。

 

参考书籍:《深入浅出MySQL》  推荐书籍! 参考链接:http://www.cnblogs.com/duodushu/p/5446384.html 

MySQL之触发器

标签:ace   spec   esc   local   alt   stat   tables   允许   链接   

人气教程排行