mysql 触发器循环执行操作

一、应用场景

添加菜单的时候默认给已设置菜单权限的用户新增此权限，本功能应该是一起写好的。但由于我比较懒，加上这个权限不是那么重要，因此就没写 添加菜单的功能，添加菜单都是在数据库中直接操作。由而引发了这个问题：使用触发器添加菜单循环向菜单权限表增加该菜单权限。大家平时不要这样使用菜单权限哈，这里是偷个懒引发这个问题。

二、上代码

这里我用的是游标的循环，其他几种可以看看网上教程

CREATE TRIGGER `menu增加` AFTER INSERT ON `tab_menu` FOR EACH ROW BEGIN
  /*定义属性*/
  declare done int default 0;/*判断循环是否关闭的标志位*/
  declare this_pid int(11);
  declare this_auth_ids varchar(255);
  /*定义游标 以及赋值 如果多个值就注意下游标取值 ***/
  declare result cursor for select p_id,menu_ids from tab_menu_auth where menu_ids != '';
  /*定义异常处理 */
  declare continue handler for not found set done = 1; 
  /*打开游标*/
  open result ;
  /*循环开始*/
  flag_loop:loop
    /*如果 done==1 有异常 结束循环*/
    if done = 1 then leave flag_loop; end if;
    /*提取游标的值  多个值的时候： fetch xxx into xxx，xxx  按照定义游标result里值的顺序赋值*/
    fetch result into this_pid,this_auth_ids;  
    /*根据游标中的数据更新menu_auth表*/
    update tab_menu_auth set menu_ids = concat(this_auth_ids,',',new.id) where p_id = this_pid;
  end loop; /*循环结束*/
 close result ;/*关闭游标*/
end;

三、简单说下

首先定义触发器insert事件after。
1.先定义变量 declare done int default 0;/判断循环是否关闭的标志位/
declare this_pid int(11);declare this_auth_ids varchar(255);第一个是用来判断是否有异常，第2,3变量是用来存储要循环的值的变量。
2. declare result cursor for select p_id,menu_ids from tab_menu_auth where menu_ids != ‘’; /查出所有设置过权限的用户id和权限ids 并赋值给reuslt/
3. flag_loop:loop /循环开始/
4. fetch result into this_pid,this_auth_ids; /提取result的值分别赋值给之前定义好的标量this_pid和this_auth_ids/
5. /根据游标中的数据更新menu_auth表/
update tab_menu_auth set menu_ids = concat(this_auth_ids,’,’,new.id) where p_id = this_pid;
/*concat(this_auth_ids,’,’,new.id)是把当前查出的ids拼接上‘，’然后再拼接上new.id。*new.id是触发器中的系统变量吧，表示新增记录的id/
6. end loop; /循环结束/

触发器(trigger): 是根据数据的机制来触发的行为, 不能主动执行, 也不能接收参数

触发器语法

create or replace trigger `触发器名称`
{before|after} trgger_event
on `表名`
{for each row}
{when trigger_condition}
trigger_body

代码解释:

1. {before|after}: 指定触发器是在触发事件之前触发还是发生之后触发
2. trigger_event: 触发事件 在DML触发器中主要是 insert(增) delete(删) update(改)
3. for each row: 指定创建的是行级触发器, 如果没有则是语句级触发器
4. when trigger_condition: 添加触发的条件
5. trigger_body: 标准的PL/SQL语句块

抛出异常语法

-- 抛出异常(编号范围:20000~-20999,话术自定义)
raise_application_error(-20001,'工资不能低于原来的工资')

触发器的执行次序

1. 执行before的语句级触发器
2. 执行before的行级触发器
3. 执行DML sql语句
4. 执行after的语句级触发器
5. 执行after的行级触发器

一、触发器分类

 DDL 触发器将激发，以响应各种数据定义语言 (DDL) 事件。 这些事件主要与以关键字 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 开头的 Transact-SQL 语句对应。 执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。

DML 触发器为特殊类型的存储过程，可在发生数据操作语言 (DML) 事件时自动生效，以便影响触发器中定义的表或视图。 DML 事件包括 INSERT、UPDATE 或 DELETE 语句。 DML 触发器可用于强制业务规则和数据完整性、查询其他表并包括复杂的 Transact-SQL 语句。 将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。 如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。
DML触发器又分为 AFTER 触发器与 INSTEAD OF 触发器
AFTER 触发器
在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器。 如果违反了约束，则永远不会执行 AFTER 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。 对于在 MERGE 语句中指定的每个 INSERT、UPDATE 或 DELETE 操作，将为每个 DML 操作触发相应的触发器。
INSTEAD OF 触发器
INSTEAD OF 触发器替代下列触发语句的标准操作。 因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。 例如，当在工资表中小时工资列的更新值超过指定值时，可以将触发器定义为产生错误消息并回滚该事务，或在将记录插入工资表中之前将新记录插入到审核记录。 INSTEAD OF 触发器的主要优点是可以使不能更新的视图支持更新。 例如，基于多个基表的视图必须使用 INSTEAD OF 触发器来支持引用多个表中数据的插入、更新和删除操作。 INSTEAD OF 触发器的另一个优点是使您得以编写这样的逻辑代码：在允许批处理的其他部分成功的同时拒绝批处理中的某些部分。

二、AFTER 触发器执行的顺序
通俗理解为instead of触发器是先于入库之前执行的，同一个表上只能有一个instead of触发器；但是可以有多个alter触发器，多个alter处理器执行的先后顺序是怎么样的呢？默认是按照触发器名称的顺序执行的，但是可以通过sql语句的方式设置顺序。
可将与表关联的 AFTER 触发器之一指定为执行每个 INSERT、DELETE 和 UPDATE 触发操作时激发的第一个或最后一个 AFTER 触发器。 在第一个和最后一个触发器之间激发的 AFTER 触发器将按未定义的顺序执行。
sql语句语法为：sp_settriggerorder @triggername = ‘MyTrigger’, @order = ‘first’, @stmttype = ‘UPDATE’

感谢参考材料：
https://docs.microsoft.com/zh-cn/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017
https://blog.csdn.net/daoyuly/article/details/3931025
https://www.cnblogs.com/BinBinGo/p/6431244.html
https://www.cnblogs.com/wghao/archive/2011/05/15/2047221.html