《MySQL必知必会》学习笔记十五(触发器)------掌握部分

MySQL必知必会知识预览
第一章——了解SQL
第二章——MySQL简介
第三章——使用MySQL
第四章——检索数据
第五章——排序检索数据
第六章——过滤数据
第七章——数据过滤
第八章——用通配符进行过滤
第九章——用正则表达式进行搜索
第十章——创建计算字段
第十一章——使用数据处理函数
第十二章——汇总数据
第十三章——分组数据
第十四章——使用子查询
第十五章——联结表
第十六章——创建高级联结
第十七章——组合查询
第十八章——全文本搜索
第十九章——插入数据
第二十章——更新和删除数据
第二十一章——创建和操纵表
第二十二章——使用视图
第二十三章——使用储存过程
第二十四章——使用游标
第二十五章——使用触发器
第二十六章——管理事务处理
第二十七章——全球化和本地化
第二十八章——安全管理
第二十九章——数据库维护
第三十章——改善性能
————————————– 华丽的分隔符 ————————————————–

触发器

什么是触发器，为什么使用触发器，如何使用触发器

————————————– ————————————– ————————————–

触发器概念

MySQL语句是在被需要的时候才会执行，存储过程也是如此。但是如果我们想在一条语句发生时，自动处理另外一条语句或者数据时，我们要怎么做？
在某个表发生更改时自动处理某些语句，这就是触发器。

触发器是MySQL响应delete 、update 、insert 、位于begin 和end语句之间的一组语句而自动执行的一条MySQL语句。其他的语句不支持触发器

使用触发器

创建触发器

在创建触发器时，需要给出4条语句（规则）

1.  唯一的触发器名；
2.  触发器关联的表；
3.  触发器应该响应的活动；
4.  触发器何时执行(处理之前或者之后)

注意：保持每个数据库的触发器名称唯一，在MySQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一个数据库中的不同表可能具有相同名字(内容不同)的触发器。这在其他每个数据库触发器名必须唯一的DBMS中是不允许的，而且以后的MySQL斑斑很有可能会使命名更加规范严格，，现在最后是在数据库范围内使用唯一的触发器名。

Create trigger 语句创建 触发器

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'; 
-------------------------------------------
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @info;
//版本问题，5之前可用 ；5以后的版本要加into 变量名

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可以在一个操作发生前或者发生后执行，这里AFTER INSERT 是指此触发器在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW ， 因此代码对每个插入行都会执行。文本Product added 将对每个插入的行显示一次。

注意：
1、触发器只有表才支持，视图，临时表都不支持触发器。
2、触发器是按照每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此，每个表最多支持六个触发器(insert，update，delete的before 和after)。
3、单一触发器不能与多个事件或多个表关联，所以，你需要一个队insert和update 操作执行的触发器，则应该定义两个触发器。
4、触发器失败：如果before 触发器失败，则MySQL将不执行请求的操作，此外，如果before触发器或者语句本身失败，MySQL则将不执行after触发器。

删除触发器

DROP TRIGGER trigger_name;
// 触发器不能修改和覆盖，只能删除，重新创建。

触发器类别

1、INSERT触发器
是在insert语句执行之前或者执行之后被执行的触发器。
1、在insert触发器代码中，可引入一个名为new的虚拟表，访问被插入的行；
2、在before insert触发器中，new中的值也可以被更新(允许更改被插入的值)；
3、对于auto_increment列，new在insert执行之前包含0，在insert执行之后包含新的自动生成值

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

创建一个名为neworder的触发器，按照AFTER INSERT ON orders 执行。在插入一个新订单到orders表时，MySQL生成一个新的订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器总是返回新的订单号。

2、DELETE触发器

Delete触发器在delete语句执行之前或者之后执行。
1、在delete触发器的代码内,可以引用一个名为OLD的虚拟表，用来访问被删除的行。
2、OLD中的值全为只读，不能更新。

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;

----------------------------------------------------------------

CREATE TABLE archive_orders(
  order_num int(11) NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL,
  cust_id int(11) NOT NULL,
  PRIMARY KEY (order_num),
  KEY fk_orders1_customers1 (cust_id),
  CONSTRAINT fk_orders1_customers1 FOREIGN KEY (cust_id) REFERENCES customers
 (cust_id)
) ENGINE=InnoDB AUTO_INCREMENT=20011 DEFAULT CHARSET=utf8

在任意订单被删除前将执行此触发器，它使用一条INSERT 语句将OLD中的值(要被删除的订单) 保存到一个名为archive_orders的存档表中(为实际使用这个例子，我们需要用与orders相同的列创建一个名为archive_orders的表)

使用BEFORE DELETE触发器的优点(相对于AFTER DELETE触发器来说)为，如果由于某种原因，订单不能存档，delete本身将被放弃。

我们在这个触发器使用了BEGIN和END语句标记触发器体。这在此例子中并不是必须的，只是为了说明使用BEGIN END 块的好处是触发器能够容纳多条SQL 语句(在BEGIN END块中一条挨着一条)。

3、UPDATE触发器

在update语句执行之前或者之后执行
1、在update触发器的代码内,可以引用一个名为OLD的虚拟表，用来访问以前(UPDATE语句之前)的值，引用一个名为NEW的虚拟表访问新更新的值。
2、在BEFORE UPDATE触发器中，NEW中的值可能也被用于更新(允许更改将要用于UPDATE语句中的值)
3、OLD中的值全为只读，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vemd_state);

保证州名缩写总是大写(不管UPFATE语句中是否给出了大写)，每次更新一行时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。

---

总结：
1、通常before用于数据的验证和净化(为了保证插入表中的数据确实是需要的数据) 也适用于update触发器。
2、与其他DBMS相比，MySQL 5中支持的触发器相当初级，未来的MySQL版本中估计会存在一些改进和增强触发器的支持。
3、创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的，如果insert，update，或者delete语句能够执行，则相关的触发器也能执行。
4、用触发器来保证数据的一致性(大小写，格式等)。在触发器中执行这种类型的处理的优点就是它总是进行这种处理，而且透明的进行，与客户机应用无关。
5、触发器的一种非常有意义的使用就是创建审计跟踪。使用触发器，把更改(如果需要，甚至还有之前和之后的状态)记录到另外一个表是非常容易的。
6、MySQL触发器不支持call语句，无法从触发器内调用存储过程。