MySQL 基础知识8 (触发器)

一、触发器概述

1. 补充说明：需要MySQL5及其以上版本支持 

2. 基本定义：是MySQL响应特定任意语句而自动执行的一条MySQL语句 (或位于BEGIN和END语句之间的一组语句)

3. 特定语句：DELETE、INSERT、UPDATE，其他MySQL语句暂不支持触发器

4. 创建特征：

(1). 唯一的触发器名称

(2). 触发器关联的表

(3). 触发器应该响应的活动(DELETE、INSERT、UPDATE)

(4). 触发器何时执行(处理之前或处理之后)

(5). 仅有表支持触发器，不支持视图和临时表

(6). 每个表每个事件每次只允许一个触发器，因此每张表最多支持6个触发器(每个INSERT、UPDATE和DELETE操作前后)

(7). 单一触发器不能与多个事件或多个表关联，若需要进行两种及以上不同的操作，则应该定义对应两个及以上的触发器

二、使用触发器

1. 创建触发器：CREATE TRIGGER (保持每个数据库的触发器名称唯一)

说明：创建一个触发器，在INSERT语句操作成功后执行，FOR EACH ROW 表示对每个操作行执行

CREATE TRIGGER newProduct AFTER 
    INSERT ON products
　　FOR EACH ROW SELECT 'Product added'  into @insertContent;

2. 测试触发器是否生效

INSERT INTO `basic`.`products`(`prod_id`, `vend_id`, `prod_name`, `prod_price`, `prod_desc`) VALUES ('Test', 1005, 'Test（1 sticks）', 12.85, 'Test, yellow, pack of 5 stick')

SELECT @insertContent;

3. 删除触发器：DROP TRIGGER

说明：触发器不能更新或覆盖;；若需要修改触发器，必须先删除它，然后重新创建

三、触发器类型

1. 使用INSERT触发器

-- 创建触发器
CREATE TRIGGER neworder AFTER INSERT ON orders    
    FOR EACH ROW SELECT 'added' INTO @order_num;
-- 插入数据
INSERT INTO orders(order_date, cust_id)
    VALUES(NOW(), 10006);
-- 检索数据    
SELECT     @order_num;

2. 使用DELETE触发器

相关说明：

(1). BEFORE DELETE触发器的优点：可将表中行数据删除前，保存到一个指定表中进行存档

(2). 使用BEGIN END语句块有利于触发器容纳多条SQL语句

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
       INSERT INTO archive_order(order_num, order_date, cust_id)
       VALUES(old.order_num, old.order_date, old.cust_id);
END;

3. 使用UPDATE触发器

相关说明：

(1). 在UPDATE触发器代码中，可以引用一个名为OLD虚拟表访问更新前的值；引用一个名为NEW虚拟表访问更新后的值

(2). 在BEFORE UPDATE触发器中，NEW中的值可能也被更新

(3). OLD虚拟表中的值全都是只读的，不能进行更新

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

四、知识小结

1. 与其他DBMS相比, MySQL 5中所支持的触发器相当初级

2. 创建触发器可能需要特殊的安全访问权限, 但触发器的执行是自动的(若指定操作语句能执行,则相关触发器也能执行)

3. 应该使用触发器保证数据的一致性, 触发器中总是进行该类型的处理,而且是透明的且和客户机应用无关

4. 触发器的一种非常有意义的使用是创建审计跟踪, 使用触发器将更改记录到另一个表中的操作非常容易

5. MySQL触发器中不支持CALL语句, 意味着不能从触发器内调用存储过程, 所需的存储过程需要复制到触发器内