深入学习MYSQL-使用触发器

触发器

每个表最多支持6个触发器，（insert，update，delete）之前和之后。

• 删除触发器

drop trigger trigger_name;
1

• insert 触发器
   在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
   在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
   对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
  下面来看例子(这里书上的例子尽然不可以，不知道是不是版本的原因)：
  首先我们创建一个表用于记录日志

CREATE TABLE product_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    prod_id VARCHAR(50),
    action VARCHAR(50),
    action_date DATETIME
);
1
2
3
4
5
6

创建一个触发器

DELIMITER $$
CREATE TRIGGER newproduct 
AFTER INSERT 
ON products 
FOR EACH ROW 
BEGIN
--这个new 虚拟表可以拿到插入后的值
   INSERT INTO product_log(prod_id, action, action_date) 
   VALUES (NEW.prod_id, 'product added', NOW());
END;$$
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11

同时我们是可以在插入之前修改值的

DELIMITER $$
CREATE TRIGGER beforeinsert 
BEFORE INSERT 
ON products 
FOR EACH ROW 
BEGIN
-- 插入之前更新值
   set NEW.prod_price = 111.0;
END;$$
INSERT INTO `products` VALUES ('ANV10', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
1
2
3
4
5
6
7
8
9
10

我们可以看到执行结果，我们插入里面写的是5.99但是最终存储是111.0是因为在插入之前我们更新了这个值

注：这里还有一点在使用before更新值的时候，我们这个列不能是外键，外键是不能更新成功的。主键可以被更新。

• update触发器
   在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
   在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
   OLD中的值全都是只读的，不能更新。
  下面我们来看一个例子：

CREATE TABLE test_update(
	id int not null auto_increment primary key,
	before_id char(50) not null,
	after_id char(50) not null
);
1
2
3
4
5

DELIMITER $$
CREATE TRIGGER afterupdate 
AFTER UPDATE 
ON products 
FOR EACH ROW 
BEGIN
   INSERT INTO test_update(before_id,after_id)values(old.prod_id,new.prod_id);
END;$$
1
2
3
4
5
6
7
8

在update之前我们也可以new中的值，这个用法和insert一样这里哦不在演示。

• delete触发器
   在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
   OLD中的值全都是只读的，不能更新。
  这里直接用了之前的表，只是重新改了个名字测试

DELIMITER $$
CREATE TRIGGER beforedelete 
BEFORE DELETE 
ON products 
FOR EACH ROW 
BEGIN
   INSERT INTO test_delete(before_id,after_id)values(old.prod_id,old.prod_id);
END;$$
1
2
3
4
5
6
7
8

这个在删除之前和删除之后都可以通过old虚拟表拿到值。