1. 个人技术分享首页

MySQL触发器的使用场景及方法实例

触发器:

触发器的使用场景以及相应版本:

触发器可以使用的MySQL版本:

  • 版本:MySQL5以上

使用场景例子:

  1. 每当增加一个顾客到某个数据库表时,都检查其电话号码格式是否正确,州的缩写是否为大写
  2. 每当订购一个产品时,都从库存数量中减去订购的数量
  3. 无论何时删除一行,都在某个存档表中保留一个副本

即:在某个表发生更改时自动处理。

如遇到触发器报错“Not allowed to return a result set from a trigger”;请划到最后看详解;

触发器的使用:

创建基本的触发器:

CREATE TRIGGER newproduct AFTER INSERT on products FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
 SET msg = "products added";
 SIGNAL SQLSTATE 'HY000' SET message_text = msg;
 
END

结果:

INSERT INTO products VALUES(‘demo2′,’1003′,’xiaoguo’,’66.6′,’hello world’)
> 1644 – products added
> 时间: 0.035s

解释:

首先创建一个触发器:

#newproduct 触发器的名字
CREATE TRIGGER newproduct

触发的时机:

BEFORE:触发器在触发他们的语句之前触发

AFTER:触发器在触发他们的语句完成后触发

在这里我们使用的after;也就是在插入结束后触发条件;

DECLARE msg VARCHAR(100);

注意:declare语句是在复合语句中声明变量的指令;如果不声明msg,执行语句时,MySQL报错;

SIGNAL SQLSTATE 'HY000' SET message_text = msg;

如果该SIGNAL语句指示特定SQLSTATE值,则该值用于表示指定的条件

“HY000”被称为“一般错误”:

如果命令出现一般错误,则会触发后面的message中的消息;

注:该语句只是个人理解,也是一知半解,如果有更好的解释,欢迎留言。

触发的条件以BEGIN开始,END结束。

触发事件:

  1. insert
  2. update
  3. delete

删除触发器:

-- 删除触发器
DROP TRIGGER newproduct;

INSERT触发器:

insert触发器在insert语句执行之前或者之后执行,需要注意以下几点:

  1. 在insert触发器代码内。可以引用一个名为NEW的虚拟表,访问被插入的行;
  2. 在before insert触发器中,NEW中的值也可以被更新(允许更改被插入的值)
  3. 对于AUTO_INCREMENT列,NEW在insert执行之前包含0,在insert执行之后包含新的自动生成值

例子:插入一个新的订单时,生成一个新的订单号保存到order_num

CREATE TRIGGER neworder AFTER INSERT ON orders for EACH ROW
 SELECT NEW.order_num into @ee;

insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;

drop TRIGGER neworder;

解释:

创建一个neworder的触发器,在插入之后执行,且对每个插入行执行,在insert中有一个与orders表一摸一样的虚表,用NEW 表示;

SELECT NEW.order_num into @a;

在虚表中找到我们插入的数据的编号,将标号保存在a变量中;

检测:

insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;

插入数据,输出插入数据的编号

删除:

drop TRIGGER neworder;

删除触发器。

例二:

在COURSE表上创建触发器,检查插入时是否出现课程名相同的记录,若有则不操作。

CREATE TRIGGER trg_course_in 
BEFORE INSERT ON course
FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(100); 
  IF EXISTS (SELECT * FROM course where cname=NEW.cname) THEN 
   SET msg='不能输入相同名称的课程'; 
   SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
  END IF; 
 
END

例三:向student表中插入信息时,检查ssex的值必须为男或女。

CREATE TRIGGER trg_ssex AFTER INSERT on student FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100);
 IF(NEW.ssex not in('男','女')) THEN
 SET msg ='性别必须为男或女'; 
 SIGNAL SQLSTATE 'HY000' SET message_text = msg;
 END IF
END

UPDATE触发器:

  1. 在update触发器的代码中,可以引用一个名为OLD的虚拟表访问以前的值,即:update未执行前的值,还可以引用一个名为NEW的虚拟表访问新更新的值;
  2. 在before update触发器中,NEW中的值可能也被更新(允许修改将要用于update语句中的值);
  3. OLD中的值全部只读,不能更新。

例一:保证州名缩写为大写

CREATE TRIGGER UPDATEevendor BEFORE UPDATE on vendors
FOR EACH ROW SET new.vend_state =UPPER(new.vend_state);

UPDATE vendors SET vend_state='hw' where vend_id='1001';
DROP TRIGGER UPDATEevendor;

注:upper:将文本转换为大写:

例二:不允许修改student表中的学号sno,如果修改该列则显示错误信息并取消操作。

CREATE TRIGGER trg_student_updateSno BEFORE UPDATE
FOR EACH ROW
BEGIN
 DECLARE msg VARCHAR(100); 
 IF NEW.sno  OLD.sno THEN 
 SET msg='不允许修改sno'; 
 SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
 END IF; 
END

DELETE触发器:

在DELETE触发器在delete语句执行之前或之后执行:

  1. 在delete触发器代码内,可以引用OLD的虚拟表,访问被删除的行;
  2. OLD中的值全部都是只读,不能更新

例子:

使用old保存将要被删除的行到一个存档表中

首先先创建一个与orders相似的表:

CREATE TABLE archive_orders LIKE orders;
-- 创建一个删除的触发器
CREATE TRIGGER deleteorder BEFORE DELETE on orders
for EACH ROW BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id) VALUES(old.order_num,old.order_date,old.cust_id);
END

解释:

在删除order表中行中信息时,将删除的信息保存到archive_orders中;

删除原表中一行:

DELETE FROM orders WHERE order_num='20014';

查看效果:

结束:

注:如果遇到触发器报错“Not allowed to return a result set from a trigger”

  1. 原因:因为从MySQL5以后不支持触发器返回结果集
  2. 解决方法:在后面语句后面添加 into @变量名
  3. 取数据:select @变量名

详细解释:https://www.programmersought.com/article/3237975256/

创建用户变量:https://www.jb51.net/article/201843.htm

文章来源于互联网:MySQL触发器的使用场景及方法实例

原创文章,作者:admin,如若转载,请注明出处:https://www.aliyunsolution.com/3419.html