MySQL 触发器学习

触发器的作用：当数据表的数据发生增删改操作的时候，会自动执行一段代码。

触发器的作用：实现一些由表事件触发的某个操作。

数据库事件：插入、更新、删除而执行的一段代码，可以设置之前还是之后。
从5.0.2版本开始支持触发器。
触发器只能定义在永久的表上，不能对临时表创建。

触发器的分类执行一个语句的触发器，执行多个语句的触发器。

（1） 我们先学习执行一条语句的触发器
数据表准备。

####################################################################
DROP TABLE IF EXISTS t_dept;
CREATE TABLE t_dept(
  deptno INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '部门表主键',
  dname VARCHAR(20) COMMENT '部门名称',
  loc VARCHAR(40) COMMENT '部门位置'
);

DROP TABLE IF EXISTS t_diary;
CREATE TABLE t_diary(
  diaryno INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '日志表主键',
  tablename VARCHAR(20) COMMENT '表名',
  diarytime DATETIME COMMENT '时间'
);

# 建议触发器的名称以“trigger_”开头
CREATE TRIGGER tri_diarytime
BEFORE INSERT ON t_dept FOR EACH ROW
INSERT INTO t_diary VALUES(NULL,'t_dept',NOW());
####################################################################

然后，我们向 t_dept 表插入一条数据。

INSERT INTO t_dept VALUES(NULL,'产品部','福州');

随后，我们就会在日志表 t_diary 里发现刚刚触发器帮我们完成的数据插入的操作。

至此，触发器的 HelloWorld 完成。

小知识：
在 MySQL 软件中，一般情况下使用“;”作为语句的结束符号。但是在我们写触发器的时候，会遇到使用“;”作为执行语句的执行符号，这样会导致触发器执行到一半，程序终止，这显然不是我们所期望发生的。
解决办法：使用 DELIMITER 。

例如：

delimiter $$

delimiter ;

（2） 执行多条 SQL 语句的触发器

#订单表
DROP TABLE IF EXISTS t_order;
CREATE TABLE IF NOT EXISTS t_order(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单主键 id' ,
  NAME VARCHAR(50) COMMENT '订单名称',
  itemcount FLOAT(10,2) COMMENT '订单数量'
);
#订单明细表
DROP TABLE IF EXISTS t_orderitem;
CREATE TABLE IF NOT EXISTS t_orderitem(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单详情主键 id',
  NAME VARCHAR(50) COMMENT '商品名称',
  price FLOAT(10,2) COMMENT '单价',
  counts INT COMMENT'数量',
  oid INT COMMENT '外键，关联订单 id'
);

然后，添加外键。

ALTER TABLE t_orderitem ADD CONSTRAINT orderitem_fk FOREIGN KEY (oid) REFERENCES t_order(id);

然后我们编写触发器：

业务需求：往明细表 t_orderitem 里面添加数据，然后就在 t_order 表里面更新price字段值。

##### 该触发器的作用，在 item 表插入数据之后 触发#####################
DELIMITER $$
CREATE TRIGGER trigger_item_insert AFTER INSERT ON t_orderitem FOR EACH ROW
BEGIN
DECLARE newid INT DEFAULT 0;
DECLARE newprice FLOAT(10,2) DEFAULT 0;
DECLARE newcounts INT DEFAULT 0;
DECLARE s FLOAT(10,2) DEFAULT 0;
SELECT oid,price,counts INTO newid,newprice,newcounts FROM t_orderitem WHERE id=NEW.id;
SET s = newprice * newcounts;
UPDATE t_order SET itemcount = itemcount + s WHERE id=newid;
END$$
DELIMITER ;

下面我们测试触发器是否生效：

# 测试：在订单表里面添加一条订单数据。
INSERT INTO t_order(NAME,itemcount) VALUE('李威的订单1',0);
# 然后在订单详情表里面添加订单详情 
INSERT INTO t_orderitem(NAME,price,counts,oid) VALUES ('花生',1000,2,1);
INSERT INTO t_orderitem(NAME,price,counts,oid) VALUES ('瓜子',200,3,1);
INSERT INTO t_orderitem(NAME,price,counts,oid) VALUES ('啤酒',10,6,1);
#  然后检查orders表的数据变化。
SELECT * FROM t_orderitem

查看刚刚编写的触发器

SHOW TRIGGERS;