SQL: 由浅入深讲解 触发器

什么是触发器呢？从名字上看我们就很容易理解了，所谓触发器具有受外部因素刺激时能做出回应刺激动作的事物。

最近碰到一个关于触发器的问题，才意识到自己对触发器这方面的知识并没有很好的掌握。于是在此总结下，我遇到的问题，并通过解决和延申这个问题，来弄懂触发器。

触发器：

CREATE TRIGGER 触发器名触发时机  触发事件ON 表名 for 层级BEGIN 执行语句END触发时机：BEFORE/AFTER 是在触发事件执行前完成还是执行后完成？触发事件：insert delete update  是被什么事件触发呢？层次：（each row）/（state）是被什么层次的事件触发呢？

现在举出一系列的问题，在解决问题的过程中，我们逐步深入理解触发器。

1）上面的是触发器最基础的结构，现在举一个简单的例子：

现在创建一个名为stu_insert_tg触发器，它依附于student表，触发事件 insert。当向student表中插入数据时，会触发这个触发器，在student_log表中写入插入时间和日志内容

CREATE TRIGGER stu_insert_tgAFTER  insert #触发事件是insert，时机是执行insert之后ON student FOR each row #每行变化都会触发触发器BEGIN insert into student_log(datetime,event) VALUES(now(),'insert into student');END

2）我们增加需求，要求在student_log表记录student表插入学生的name

DROP TRIGGER stu_insert_tg;CREATE TRIGGER stu_insert_tgAFTER  insertON student FOR each rowBEGIN insert into student_log(datetime,event,name) VALUES(now(),'insert into student',new.name);END

注意在倒数第二行处的 new.name ,这代表的是新插入的数据中的name字段。将这个值放入student_log表中的name字段中。以此为契机，介绍下new和old。

图片来自于 https://www.cnblogs.com/phpper/p/7587031.html

3）这时候我们就有疑惑了，如果我以此插入多条数据，那么student_log表会记录几条数据呢？

INSERT student(name) VALUES(‘a’),(‘b’),(‘c’)；

student_log表中的结果：

datetime event name
2019-05-29 19:43:09 insert into student a
2019-05-29 19:43:09 insert into student b
2019-05-29 19:43:09 insert into student c

我们发现，插入的三条数据里都被记录了。

4）在 SQL Server中 deleted/ Inserted表代表着更改的值，那么Mysql里有吗？答案是否定的，没有。

5) 现在我们增加对student表插入数据的要求，插入的数据里，name长度大于5，那么不允许插入，并且显示提示。现在我们尝试以下写法：注意我们现在使用的不再是AFTER而是BEFORE

CREATE TRIGGER stu_insert_tgAFTER insertON student FOR each rowBEGINif(CHAR_LENGTH(new.name)<=5) THEN insert into student(name) VALUES(new.name);ELSE ROLLBACK;END IF;END

我们发现他会提示报错：Explicit or implicit commit is not allowed in stored function or trigger.查询资料后我们发现， START TRANS-ACTION,COMMIT或ROLLBACK 是不被允许在触发器中使用的。而我们写的内容里正好有ROLLBACK，现在我们去除ROLLBACK。 注意我们现在使用的不再是AFTER而是BEFORE

CREATE TRIGGER stu_insert_tg
 BEFORE  insert
 ON student FOR each row
 BEGIN
 if(CHAR_LENGTH(new.name)<=5) THEN
  insert into student(name) VALUES(new.name);
 END IF;
 END

这时候，我们会发现建立时不再出现报错，但是并不能达到我们预想。当输入的name长度大于5时，它是照常输入到student表中的。而当长度小于5时反而会出现报错 Can’t update table ‘student’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 提示我们进入了插入操作的死循环中。但是这给我们了一个提示，虽然Mysql中没有ROLLBACK，但是当触发器错误时，原有语句也不能执行了。

CREATE TRIGGER stu_insert_tg
 BEFORE  insert
 ON student FOR each row
 BEGIN
 if(CHAR_LENGTH(new.name)>5) THEN
    insert into student(name) VALUES(new.name); 
 END IF;
 END

于是我们现在诱导不符合要求的数据触发错误，来阻止数据的插入，对符合要求的数据则不做操作。发现，完美的解决了这个问题。

终极问题：

我遇到的问题吧，现在有这样的三张有关医院的表：

处方（药品编号，数量，医生编号）

医生 （编号，姓名，科室，职称）

药品（编号，名称，价格，厂商）

现在在药品表中需要加入一个delete触发器，如果你想删除的药品曾经被卖出去过（这意味着会出现在处方表中），那么这个删除操作是不应该被执行的。

这个问题的难度就上升了些，因为这此不在只涉及一个表了，我们是在两张表中对问题进行的判断。这时候我们就需要存储过程中有关变量的知识了。这里提供一个链接

DROP TRIGGER if EXISTS tg_delete_medicine;#删除已存在同名触发器
 CREATE TRIGGER  tg_delete_medicine
 BEFORE  delete#delete类型的触发器
  ON medicine FOR each row
  BEGIN
    DECLARE useNumber INT; #局部变量当前药品在处方中出现次数
    SELECT COUNT(*) INTO useNumber FROM prescription WHERE mid=old.id;#注意这里对useNumber的赋值方法，触发器中不允许使用SELECT useNumber=COUNT(*)...这种形式
  if(useNumber<>0) THEN判断是否在处方中出现过
     insert into medicine(name) VALUES(old.name); #诱导执行失败
  END IF;
  END