使用触发器

野性酷女 2022-05-06 10:40 220阅读 0赞

## 1.什么是触发器 ##

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

## 2.创建触发器 ##

创建触发器的语法如下：

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

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

##### 创建有多个执行语句的触发器 #####

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
    ON 表名 FOR EACH ROW
    BEGIN
        执行语句列表
    END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

\*\*tips：\*\*一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

mysql> DELIMITER ||
    mysql> CREATE TRIGGER demo BEFORE DELETE
        -> ON users FOR EACH ROW
        -> BEGIN
        -> INSERT INTO logs VALUES(NOW());
        -> INSERT INTO logs VALUES(NOW());
        -> END
        -> ||
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

tigger\_event：

<table> 
 <thead> 
  <tr> 
   <th>触发器类型</th> 
   <th>激活触发器的语句</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>insert型触发器</td> 
   <td>insert,load data,replace</td> 
  </tr> 
  <tr> 
   <td>update型触发器</td> 
   <td>update</td> 
  </tr> 
  <tr> 
   <td>delete触发器</td> 
   <td>delete,replace</td> 
  </tr> 
 </tbody> 
</table>

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

触发器可以是一条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的使用:

<table> 
 <thead> 
  <tr> 
   <th>触发器类型</th> 
   <th>new和old的使用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>insert型触发器</td> 
   <td>new表示将要或者已经新增的数据</td> 
  </tr> 
  <tr> 
   <td>update型触发器</td> 
   <td>old用来表示将要或者已经被删除的语句吗，new表示将要或者已经被修改的数据</td> 
  </tr> 
  <tr> 
   <td>delete型触发器</td> 
   <td>old表示将要或者已经被删除的数据</td> 
  </tr> 
 </tbody> 
</table>

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

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

说了这么多现在我们来创建一个触发器吧！

现在有表如下：  
用户users表

CREATE TABLE `users` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
      `add_time` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`(250)) USING BTREE
    ) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;

日志logs表：

CREATE TABLE `logs` (
      `Id` int(11) NOT NULL AUTO_INCREMENT,
      `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
      PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。

创建触发器：

DELIMITER $
    CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
    BEGIN
    DECLARE s1 VARCHAR(40)character set utf8;
    DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集
    SET s2 = " is created";
    SET s1 = CONCAT(NEW.name,s2);     #函数CONCAT可以将字符串连接
    INSERT INTO logs(log) values(s1);
    END $
    DELIMITER ;

这里再啰嗦几句：

\*\*tips：\*\*SHOW TRIGGERS语句无法查询指定的触发器

##### 在triggers表中查看触发器信息 #####

SELECT * FROM information_schema.triggers;

结果显示了所有触发器的详细信息，同时，该方法可以查询制定触发器的详细信息

SELECT * from information_schema.`TRIGGERS` where TRIGGER_NAME = 'user_log';

\*\*tips：\*\*所有触发器信息都存储在information\_schema数据库下的triggers表中

可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER\_NAME字段指定查询

回到上面,我们创建好了触发器,继续在users中插入数据并查看数据：

insert into users(name,add_time) values('周伯通',now());

mysql> SELECT * from logs;
    +----+-------------------+
    | Id | log               |
    +----+-------------------+
    |  1 | 周伯通 is created |
    |  2 | 周伯通 is created |
    |  3 | 周伯通 is created |
    +----+-------------------+
    3 rows in set

通过上面的例子，可以看到只需要在users中插入用户的信息，日志会自动记录到logs表中，这也许就是触发器给我带来的便捷吧！

## 限制和注意事项 ##

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

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

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

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

## 总结 ##

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

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用：

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

# 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。 #